-- ============================================================================ -- CROWNE PLAZA RIYADH RDC HOTEL FOOD-SERVICE PLATFORM -- MERGED DATABASE INSTALLER - PHASES 1 THROUGH 12 -- Target: MySQL 8+ / cPanel MariaDB compatible -- Includes MariaDB ERROR 1901 billing hotfix, Phase 11 engagement, and Phase 12 licensing. -- Fresh install: import this file, configure .env/APP_KEY/license public key, then run php bin/seed.php. -- ============================================================================ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 1; -- ============================================================================ -- MIGRATION: 001_core.sql -- ============================================================================ CREATE TABLE IF NOT EXISTS schema_migrations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, migration VARCHAR(255) NOT NULL UNIQUE, applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS hotels ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, code VARCHAR(50) NOT NULL, name VARCHAR(200) NOT NULL, short_name VARCHAR(120) NOT NULL, city VARCHAR(120) NOT NULL, country VARCHAR(120) NOT NULL, country_code CHAR(2) NOT NULL DEFAULT 'SA', currency CHAR(3) NOT NULL DEFAULT 'SAR', timezone VARCHAR(64) NOT NULL DEFAULT 'Asia/Riyadh', default_language VARCHAR(10) NOT NULL DEFAULT 'en', additional_language VARCHAR(10) NULL, status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_hotels_public_id (public_id), UNIQUE KEY uq_hotels_code (code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS hotel_settings ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, hotel_id BIGINT UNSIGNED NOT NULL, setting_key VARCHAR(120) NOT NULL, setting_value TEXT NULL, value_type ENUM('STRING','INTEGER','DECIMAL','BOOLEAN','JSON') NOT NULL DEFAULT 'STRING', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_hotel_setting (hotel_id, setting_key), CONSTRAINT fk_hotel_settings_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS media ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, hotel_id BIGINT UNSIGNED NOT NULL, disk VARCHAR(50) NOT NULL DEFAULT 'public', path VARCHAR(500) NOT NULL, original_name VARCHAR(255) NULL, mime_type VARCHAR(120) NOT NULL, size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0, width INT UNSIGNED NULL, height INT UNSIGNED NULL, uploaded_by BIGINT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_media_hotel (hotel_id), CONSTRAINT fk_media_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS tax_rates ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, hotel_id BIGINT UNSIGNED NOT NULL, code VARCHAR(50) NOT NULL, name VARCHAR(120) NOT NULL, rate DECIMAL(7,4) NOT NULL, valid_from DATE NULL, valid_to DATE NULL, status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_tax_code (hotel_id, code), CONSTRAINT fk_tax_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================ -- MIGRATION: 002_auth.sql -- ============================================================================ CREATE TABLE IF NOT EXISTS users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, hotel_id BIGINT UNSIGNED NULL, email VARCHAR(190) NOT NULL, username VARCHAR(100) NOT NULL, password_hash VARCHAR(255) NOT NULL, name VARCHAR(160) NOT NULL, status ENUM('ACTIVE','DISABLED') NOT NULL DEFAULT 'ACTIVE', failed_login_count SMALLINT UNSIGNED NOT NULL DEFAULT 0, locked_until DATETIME NULL, last_login_at DATETIME NULL, last_login_ip VARCHAR(45) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_users_email (email), UNIQUE KEY uq_users_username (username), INDEX idx_users_hotel (hotel_id), CONSTRAINT fk_users_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS roles ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, hotel_id BIGINT UNSIGNED NULL, code VARCHAR(80) NOT NULL, name VARCHAR(120) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_role_code (hotel_id, code), CONSTRAINT fk_roles_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS permissions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(120) NOT NULL UNIQUE, name VARCHAR(160) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS user_roles ( user_id BIGINT UNSIGNED NOT NULL, role_id BIGINT UNSIGNED NOT NULL, assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (user_id, role_id), CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS role_permissions ( role_id BIGINT UNSIGNED NOT NULL, permission_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (role_id, permission_id), CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE, CONSTRAINT fk_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================ -- MIGRATION: 003_dining_rooms_qr.sql -- ============================================================================ CREATE TABLE IF NOT EXISTS kitchens ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, hotel_id BIGINT UNSIGNED NOT NULL, code VARCHAR(80) NOT NULL, name VARCHAR(160) NOT NULL, type ENUM('RESTAURANT','ROOM_SERVICE','CENTRAL') NOT NULL DEFAULT 'RESTAURANT', status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_kitchen_code (hotel_id, code), CONSTRAINT fk_kitchens_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS kitchen_stations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, kitchen_id BIGINT UNSIGNED NOT NULL, code VARCHAR(80) NOT NULL, name VARCHAR(160) NOT NULL, display_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_station_code (kitchen_id, code), CONSTRAINT fk_stations_kitchen FOREIGN KEY (kitchen_id) REFERENCES kitchens(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS restaurants ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, hotel_id BIGINT UNSIGNED NOT NULL, public_id CHAR(26) NOT NULL, kitchen_id BIGINT UNSIGNED NULL, code VARCHAR(80) NOT NULL, name VARCHAR(160) NOT NULL, type ENUM('RESTAURANT','CAFE','ROOM_SERVICE') NOT NULL, description TEXT NULL, logo_media_id BIGINT UNSIGNED NULL, display_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, discoverable TINYINT(1) NOT NULL DEFAULT 1, status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_restaurant_public_id (public_id), UNIQUE KEY uq_restaurant_code (hotel_id, code), INDEX idx_restaurant_hotel_status (hotel_id, status, display_order), CONSTRAINT fk_restaurant_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE, CONSTRAINT fk_restaurant_kitchen FOREIGN KEY (kitchen_id) REFERENCES kitchens(id) ON DELETE SET NULL, CONSTRAINT fk_restaurant_logo FOREIGN KEY (logo_media_id) REFERENCES media(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS restaurant_hours ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, restaurant_id BIGINT UNSIGNED NOT NULL, weekday TINYINT UNSIGNED NOT NULL COMMENT '1=Monday ... 7=Sunday', opens_at TIME NULL, closes_at TIME NULL, is_closed TINYINT(1) NOT NULL DEFAULT 0, valid_from DATE NULL, valid_to DATE NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_restaurant_hours_lookup (restaurant_id, weekday, valid_from, valid_to), CONSTRAINT fk_restaurant_hours_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE, CONSTRAINT chk_restaurant_hours_weekday CHECK (weekday BETWEEN 1 AND 7) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS restaurant_service_channels ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, restaurant_id BIGINT UNSIGNED NOT NULL, service_type ENUM('DINE_IN','ROOM_SERVICE','TAKEAWAY','PRE_ORDER') NOT NULL, enabled TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_restaurant_service_channel (restaurant_id, service_type), CONSTRAINT fk_restaurant_channels_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS restaurant_tables ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, restaurant_id BIGINT UNSIGNED NOT NULL, public_id CHAR(26) NOT NULL, code VARCHAR(40) NOT NULL, name VARCHAR(100) NOT NULL, section VARCHAR(100) NULL, capacity SMALLINT UNSIGNED NOT NULL DEFAULT 2, minimum_capacity SMALLINT UNSIGNED NOT NULL DEFAULT 1, maximum_capacity SMALLINT UNSIGNED NOT NULL DEFAULT 2, status ENUM('AVAILABLE','RESERVED','OCCUPIED','CLEANING','OUT_OF_SERVICE') NOT NULL DEFAULT 'AVAILABLE', map_x DECIMAL(10,2) NULL, map_y DECIMAL(10,2) NULL, map_width DECIMAL(10,2) NULL, map_height DECIMAL(10,2) NULL, map_rotation DECIMAL(6,2) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_table_public_id (public_id), UNIQUE KEY uq_table_code (restaurant_id, code), INDEX idx_tables_restaurant_status (restaurant_id, status), CONSTRAINT fk_tables_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE, CONSTRAINT chk_table_capacity CHECK (minimum_capacity <= capacity AND capacity <= maximum_capacity) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS hotel_rooms ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, hotel_id BIGINT UNSIGNED NOT NULL, public_id CHAR(26) NOT NULL, room_number VARCHAR(50) NOT NULL, floor VARCHAR(50) NULL, section VARCHAR(100) NULL, status ENUM('ACTIVE','INACTIVE','OUT_OF_SERVICE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_room_public_id (public_id), UNIQUE KEY uq_room_number (hotel_id, room_number), INDEX idx_rooms_hotel_status (hotel_id, status), CONSTRAINT fk_rooms_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS table_qr_codes ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, table_id BIGINT UNSIGNED NOT NULL, token_hash CHAR(64) NOT NULL, token_ciphertext TEXT NOT NULL, status ENUM('ACTIVE','REVOKED') NOT NULL DEFAULT 'ACTIVE', issued_at DATETIME NOT NULL, revoked_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_table_qr_hash (token_hash), INDEX idx_table_qr_active (table_id, status), CONSTRAINT fk_table_qr_table FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS room_qr_codes ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, room_id BIGINT UNSIGNED NOT NULL, token_hash CHAR(64) NOT NULL, token_ciphertext TEXT NOT NULL, status ENUM('ACTIVE','REVOKED') NOT NULL DEFAULT 'ACTIVE', issued_at DATETIME NOT NULL, revoked_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_room_qr_hash (token_hash), INDEX idx_room_qr_active (room_id, status), CONSTRAINT fk_room_qr_room FOREIGN KEY (room_id) REFERENCES hotel_rooms(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS table_sessions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, table_id BIGINT UNSIGNED NOT NULL, status ENUM('ACTIVE','CLOSED','EXPIRED') NOT NULL DEFAULT 'ACTIVE', started_at DATETIME NOT NULL, closed_at DATETIME NULL, expires_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_table_session_public (public_id), INDEX idx_table_session_active (table_id, status), CONSTRAINT fk_table_sessions_table FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS room_sessions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, room_id BIGINT UNSIGNED NOT NULL, source ENUM('QR_DEMO','PMS','STAFF') NOT NULL DEFAULT 'QR_DEMO', status ENUM('ACTIVE','CHECKED_OUT','EXPIRED') NOT NULL DEFAULT 'ACTIVE', checkin_at DATETIME NOT NULL, checkout_at DATETIME NULL, expires_at DATETIME NULL, room_charge_authorized TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_room_session_public (public_id), INDEX idx_room_session_active (room_id, status), CONSTRAINT fk_room_sessions_room FOREIGN KEY (room_id) REFERENCES hotel_rooms(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS customer_sessions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, table_session_id BIGINT UNSIGNED NULL, room_session_id BIGINT UNSIGNED NULL, token_hash CHAR(64) NOT NULL, device_hash CHAR(64) NULL, last_seen_at DATETIME NOT NULL, expires_at DATETIME NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_customer_session_public (public_id), UNIQUE KEY uq_customer_token_hash (token_hash), INDEX idx_customer_table_session (table_session_id), INDEX idx_customer_room_session (room_session_id), CONSTRAINT fk_customer_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE, CONSTRAINT fk_customer_table_session FOREIGN KEY (table_session_id) REFERENCES table_sessions(id) ON DELETE CASCADE, CONSTRAINT fk_customer_room_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE CASCADE, CONSTRAINT chk_customer_context CHECK ((table_session_id IS NOT NULL) OR (room_session_id IS NOT NULL)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================ -- MIGRATION: 004_audit.sql -- ============================================================================ CREATE TABLE IF NOT EXISTS audit_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, hotel_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL, customer_session_id BIGINT UNSIGNED NULL, action VARCHAR(120) NOT NULL, entity_type VARCHAR(120) NOT NULL, entity_id VARCHAR(120) NULL, old_values_json JSON NULL, new_values_json JSON NULL, ip_address VARCHAR(45) NULL, user_agent VARCHAR(1000) NULL, request_id CHAR(36) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_audit_hotel_created (hotel_id, created_at), INDEX idx_audit_entity (entity_type, entity_id), INDEX idx_audit_user (user_id, created_at), CONSTRAINT fk_audit_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE SET NULL, CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_audit_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================ -- MIGRATION: 005_rate_limits.sql -- ============================================================================ CREATE TABLE IF NOT EXISTS rate_limits ( bucket_key CHAR(64) PRIMARY KEY, scope VARCHAR(80) NOT NULL, hits INT UNSIGNED NOT NULL DEFAULT 1, window_started_at DATETIME NOT NULL, expires_at DATETIME NOT NULL, INDEX idx_rate_limits_expires (expires_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================ -- MIGRATION: 006_menu.sql -- ============================================================================ CREATE TABLE IF NOT EXISTS categories ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, restaurant_id BIGINT UNSIGNED NOT NULL, parent_id BIGINT UNSIGNED NULL, code VARCHAR(80) NOT NULL, name VARCHAR(160) NOT NULL, description TEXT NULL, display_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_category_public_id (public_id), UNIQUE KEY uq_category_code (restaurant_id, code), INDEX idx_categories_restaurant_status (restaurant_id, status, display_order), CONSTRAINT fk_categories_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE, CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS ingredients ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, hotel_id BIGINT UNSIGNED NOT NULL, code VARCHAR(80) NOT NULL, name VARCHAR(160) NOT NULL, status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_ingredient_code (hotel_id, code), INDEX idx_ingredients_hotel_status (hotel_id, status, name), CONSTRAINT fk_ingredients_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS allergens ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, hotel_id BIGINT UNSIGNED NOT NULL, code VARCHAR(80) NOT NULL, name VARCHAR(160) NOT NULL, description VARCHAR(500) NULL, status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_allergen_code (hotel_id, code), INDEX idx_allergens_hotel_status (hotel_id, status, name), CONSTRAINT fk_allergens_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS addons ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, tax_rate_id BIGINT UNSIGNED NULL, code VARCHAR(80) NOT NULL, name VARCHAR(160) NOT NULL, description VARCHAR(500) NULL, price DECIMAL(12,2) NOT NULL DEFAULT 0.00, status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_addon_public_id (public_id), UNIQUE KEY uq_addon_code (hotel_id, code), INDEX idx_addons_hotel_status (hotel_id, status, name), CONSTRAINT fk_addons_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE, CONSTRAINT fk_addons_tax FOREIGN KEY (tax_rate_id) REFERENCES tax_rates(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS menu_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, restaurant_id BIGINT UNSIGNED NOT NULL, category_id BIGINT UNSIGNED NOT NULL, tax_rate_id BIGINT UNSIGNED NULL, name VARCHAR(180) NOT NULL, description TEXT NULL, price DECIMAL(12,2) NOT NULL, calories INT UNSIGNED NULL, preparation_time SMALLINT UNSIGNED NULL COMMENT 'minutes', serving_time SMALLINT UNSIGNED NULL COMMENT 'minutes', display_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, featured TINYINT(1) NOT NULL DEFAULT 0, status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_menu_item_public_id (public_id), INDEX idx_menu_items_restaurant_category (restaurant_id, category_id, status, display_order), INDEX idx_menu_items_status (restaurant_id, status), CONSTRAINT fk_menu_items_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE, CONSTRAINT fk_menu_items_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT, CONSTRAINT fk_menu_items_tax FOREIGN KEY (tax_rate_id) REFERENCES tax_rates(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS menu_item_variants ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, menu_item_id BIGINT UNSIGNED NOT NULL, code VARCHAR(80) NOT NULL, name VARCHAR(120) NOT NULL, price_delta DECIMAL(12,2) NOT NULL DEFAULT 0.00, display_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_menu_variant_code (menu_item_id, code), CONSTRAINT fk_menu_variants_item FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS menu_item_flavours ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, menu_item_id BIGINT UNSIGNED NOT NULL, code VARCHAR(80) NOT NULL, name VARCHAR(120) NOT NULL, price_delta DECIMAL(12,2) NOT NULL DEFAULT 0.00, display_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_menu_flavour_code (menu_item_id, code), CONSTRAINT fk_menu_flavours_item FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS menu_item_addons ( menu_item_id BIGINT UNSIGNED NOT NULL, addon_id BIGINT UNSIGNED NOT NULL, required TINYINT(1) NOT NULL DEFAULT 0, min_qty SMALLINT UNSIGNED NOT NULL DEFAULT 0, max_qty SMALLINT UNSIGNED NOT NULL DEFAULT 1, display_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (menu_item_id, addon_id), CONSTRAINT fk_menu_item_addons_item FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE, CONSTRAINT fk_menu_item_addons_addon FOREIGN KEY (addon_id) REFERENCES addons(id) ON DELETE CASCADE, CONSTRAINT chk_menu_item_addon_qty CHECK (min_qty <= max_qty) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS menu_item_ingredients ( menu_item_id BIGINT UNSIGNED NOT NULL, ingredient_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (menu_item_id, ingredient_id), CONSTRAINT fk_menu_item_ingredients_item FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE, CONSTRAINT fk_menu_item_ingredients_ingredient FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS menu_item_allergens ( menu_item_id BIGINT UNSIGNED NOT NULL, allergen_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (menu_item_id, allergen_id), CONSTRAINT fk_menu_item_allergens_item FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE, CONSTRAINT fk_menu_item_allergens_allergen FOREIGN KEY (allergen_id) REFERENCES allergens(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS menu_item_images ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, menu_item_id BIGINT UNSIGNED NOT NULL, media_id BIGINT UNSIGNED NOT NULL, alt_text VARCHAR(255) NULL, display_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, is_primary TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_menu_item_media (menu_item_id, media_id), INDEX idx_menu_images_primary (menu_item_id, is_primary, display_order), CONSTRAINT fk_menu_images_item FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE, CONSTRAINT fk_menu_images_media FOREIGN KEY (media_id) REFERENCES media(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS availability_rules ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, menu_item_id BIGINT UNSIGNED NOT NULL, name VARCHAR(160) NOT NULL, weekday_mask SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 means every day; bits 0..6 Monday..Sunday', month_mask SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 means every month; bits 0..11 January..December', season VARCHAR(80) NULL, date_from DATE NULL, date_to DATE NULL, time_from TIME NULL, time_to TIME NULL, display_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_availability_item_status (menu_item_id, status, display_order), CONSTRAINT fk_availability_item FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE, CONSTRAINT chk_availability_dates CHECK (date_from IS NULL OR date_to IS NULL OR date_from <= date_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS kitchen_routing ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, restaurant_id BIGINT UNSIGNED NOT NULL, category_id BIGINT UNSIGNED NULL, menu_item_id BIGINT UNSIGNED NULL, kitchen_id BIGINT UNSIGNED NOT NULL, station_id BIGINT UNSIGNED NULL, priority SMALLINT UNSIGNED NOT NULL DEFAULT 100, status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_kitchen_routing_lookup (restaurant_id, menu_item_id, category_id, status, priority), CONSTRAINT fk_kitchen_routing_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE, CONSTRAINT fk_kitchen_routing_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE, CONSTRAINT fk_kitchen_routing_item FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE, CONSTRAINT fk_kitchen_routing_kitchen FOREIGN KEY (kitchen_id) REFERENCES kitchens(id) ON DELETE CASCADE, CONSTRAINT fk_kitchen_routing_station FOREIGN KEY (station_id) REFERENCES kitchen_stations(id) ON DELETE SET NULL, CONSTRAINT chk_kitchen_routing_target CHECK (category_id IS NOT NULL OR menu_item_id IS NOT NULL) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================ -- MIGRATION: 007_ordering.sql -- ============================================================================ CREATE TABLE IF NOT EXISTS carts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, restaurant_id BIGINT UNSIGNED NOT NULL, customer_session_id BIGINT UNSIGNED NOT NULL, table_session_id BIGINT UNSIGNED NULL, room_session_id BIGINT UNSIGNED NULL, service_type ENUM('DINE_IN','ROOM_SERVICE','TAKEAWAY','PRE_ORDER') NOT NULL, status ENUM('ACTIVE','ORDERED','ABANDONED') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_cart_public_id (public_id), INDEX idx_cart_customer_status (customer_session_id,status,updated_at), INDEX idx_cart_context (table_session_id,room_session_id,status), CONSTRAINT fk_carts_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE, CONSTRAINT fk_carts_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE RESTRICT, CONSTRAINT fk_carts_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE CASCADE, CONSTRAINT fk_carts_table_session FOREIGN KEY (table_session_id) REFERENCES table_sessions(id) ON DELETE CASCADE, CONSTRAINT fk_carts_room_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE CASCADE, CONSTRAINT chk_cart_context CHECK ((table_session_id IS NOT NULL) OR (room_session_id IS NOT NULL)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS cart_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, cart_id BIGINT UNSIGNED NOT NULL, menu_item_id BIGINT UNSIGNED NOT NULL, variant_id BIGINT UNSIGNED NULL, variant_code_snapshot VARCHAR(80) NULL, flavour_id BIGINT UNSIGNED NULL, flavour_code_snapshot VARCHAR(80) NULL, quantity SMALLINT UNSIGNED NOT NULL DEFAULT 1, special_instruction VARCHAR(500) NULL, requested_course VARCHAR(40) NULL, requested_serving_time DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_cart_item_public_id (public_id), INDEX idx_cart_items_cart (cart_id,created_at), CONSTRAINT fk_cart_items_cart FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE, CONSTRAINT fk_cart_items_menu FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE, CONSTRAINT fk_cart_items_variant FOREIGN KEY (variant_id) REFERENCES menu_item_variants(id) ON DELETE SET NULL, CONSTRAINT fk_cart_items_flavour FOREIGN KEY (flavour_id) REFERENCES menu_item_flavours(id) ON DELETE SET NULL, CONSTRAINT chk_cart_item_quantity CHECK (quantity BETWEEN 1 AND 50) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS cart_item_addons ( cart_item_id BIGINT UNSIGNED NOT NULL, addon_id BIGINT UNSIGNED NOT NULL, quantity SMALLINT UNSIGNED NOT NULL DEFAULT 1, PRIMARY KEY (cart_item_id,addon_id), CONSTRAINT fk_cart_item_addons_item FOREIGN KEY (cart_item_id) REFERENCES cart_items(id) ON DELETE CASCADE, CONSTRAINT fk_cart_item_addons_addon FOREIGN KEY (addon_id) REFERENCES addons(id) ON DELETE RESTRICT, CONSTRAINT chk_cart_addon_quantity CHECK (quantity BETWEEN 1 AND 20) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, order_number VARCHAR(40) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, restaurant_id BIGINT UNSIGNED NOT NULL, table_session_id BIGINT UNSIGNED NULL, room_session_id BIGINT UNSIGNED NULL, customer_session_id BIGINT UNSIGNED NOT NULL, reservation_id BIGINT UNSIGNED NULL COMMENT 'Foreign key added when reservation module is installed', service_type ENUM('DINE_IN','ROOM_SERVICE','TAKEAWAY','PRE_ORDER') NOT NULL, status ENUM('RECEIVED','ACCEPTED','PREPARING','READY','SERVED','CANCELLED') NOT NULL DEFAULT 'RECEIVED', currency CHAR(3) NOT NULL, subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00, discount DECIMAL(12,2) NOT NULL DEFAULT 0.00, tax DECIMAL(12,2) NOT NULL DEFAULT 0.00, total DECIMAL(12,2) NOT NULL DEFAULT 0.00, requested_time DATETIME NULL, estimated_ready_time DATETIME NULL, actual_ready_time DATETIME NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_order_public_id (public_id), UNIQUE KEY uq_order_number (hotel_id,order_number), INDEX idx_orders_hotel_status (hotel_id,status,created_at), INDEX idx_orders_restaurant_status (restaurant_id,status,created_at), INDEX idx_orders_table_session (table_session_id,created_at), INDEX idx_orders_room_session (room_session_id,created_at), INDEX idx_orders_customer (customer_session_id,created_at), CONSTRAINT fk_orders_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE RESTRICT, CONSTRAINT fk_orders_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE RESTRICT, CONSTRAINT fk_orders_table_session FOREIGN KEY (table_session_id) REFERENCES table_sessions(id) ON DELETE SET NULL, CONSTRAINT fk_orders_room_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE SET NULL, CONSTRAINT fk_orders_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS order_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, menu_item_id BIGINT UNSIGNED NULL, item_name_snapshot VARCHAR(180) NOT NULL, item_description_snapshot TEXT NULL, price_snapshot DECIMAL(12,2) NOT NULL, tax_snapshot DECIMAL(7,4) NOT NULL DEFAULT 0.0000, quantity SMALLINT UNSIGNED NOT NULL, variant VARCHAR(120) NULL, variant_price_snapshot DECIMAL(12,2) NOT NULL DEFAULT 0.00, flavour VARCHAR(120) NULL, flavour_price_snapshot DECIMAL(12,2) NOT NULL DEFAULT 0.00, unit_subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00, unit_tax DECIMAL(12,2) NOT NULL DEFAULT 0.00, unit_total DECIMAL(12,2) NOT NULL DEFAULT 0.00, line_subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00, line_tax DECIMAL(12,2) NOT NULL DEFAULT 0.00, line_total DECIMAL(12,2) NOT NULL DEFAULT 0.00, special_instruction VARCHAR(500) NULL, preparation_time SMALLINT UNSIGNED NULL, requested_course VARCHAR(40) NULL, requested_serving_time DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_order_items_order (order_id,id), CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, CONSTRAINT fk_order_items_menu FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS order_item_addons ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_item_id BIGINT UNSIGNED NOT NULL, addon_id BIGINT UNSIGNED NULL, addon_name_snapshot VARCHAR(160) NOT NULL, price_snapshot DECIMAL(12,2) NOT NULL, tax_snapshot DECIMAL(7,4) NOT NULL DEFAULT 0.0000, quantity SMALLINT UNSIGNED NOT NULL DEFAULT 1, subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00, tax DECIMAL(12,2) NOT NULL DEFAULT 0.00, total DECIMAL(12,2) NOT NULL DEFAULT 0.00, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_order_item_addons_item (order_item_id), CONSTRAINT fk_order_item_addons_order_item FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE, CONSTRAINT fk_order_item_addons_addon FOREIGN KEY (addon_id) REFERENCES addons(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS order_courses ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, course_code VARCHAR(40) NOT NULL, sequence SMALLINT UNSIGNED NOT NULL DEFAULT 10, requested_serving_time DATETIME NULL, status ENUM('PENDING','RELEASED','SERVED','CANCELLED') NOT NULL DEFAULT 'PENDING', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_order_course (order_id,course_code), CONSTRAINT fk_order_courses_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS order_status_history ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, old_status VARCHAR(40) NULL, new_status VARCHAR(40) NOT NULL, user_id BIGINT UNSIGNED NULL, customer_session_id BIGINT UNSIGNED NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_order_status_history (order_id,created_at), CONSTRAINT fk_order_history_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, CONSTRAINT fk_order_history_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_order_history_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS idempotency_keys ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, hotel_id BIGINT UNSIGNED NOT NULL, scope VARCHAR(80) NOT NULL, idempotency_key_hash CHAR(64) NOT NULL, request_hash CHAR(64) NOT NULL, resource_type VARCHAR(80) NULL, resource_id BIGINT UNSIGNED NULL, expires_at DATETIME NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_idempotency_scope (hotel_id,scope,idempotency_key_hash), INDEX idx_idempotency_expiry (expires_at), CONSTRAINT fk_idempotency_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO permissions (code,name) VALUES ('orders.view','View orders'), ('orders.manage','Manage orders') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('SUPER_ADMIN','HOTEL_ADMIN','RESTAURANT_MANAGER','ROOM_SERVICE_MANAGER') AND p.code IN ('orders.view','orders.manage'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('WAITER','ROOM_SERVICE_STAFF','KITCHEN_MANAGER','READ_ONLY_MANAGER') AND p.code='orders.view'; INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'menu_prices_include_tax','1','BOOLEAN' FROM hotels ON DUPLICATE KEY UPDATE setting_value=setting_value; -- ============================================================================ -- MIGRATION: 008_kitchen_kds.sql -- ============================================================================ CREATE TABLE IF NOT EXISTS kitchen_devices ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, kitchen_id BIGINT UNSIGNED NOT NULL, station_id BIGINT UNSIGNED NULL, name VARCHAR(160) NOT NULL, device_token_hash CHAR(64) NULL, last_seen_at DATETIME NULL, status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_kitchen_devices (kitchen_id,station_id,status), CONSTRAINT fk_kitchen_devices_kitchen FOREIGN KEY (kitchen_id) REFERENCES kitchens(id) ON DELETE CASCADE, CONSTRAINT fk_kitchen_devices_station FOREIGN KEY (station_id) REFERENCES kitchen_stations(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS kitchen_orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, order_id BIGINT UNSIGNED NOT NULL, kitchen_id BIGINT UNSIGNED NOT NULL, station_id BIGINT UNSIGNED NULL, ticket_number VARCHAR(60) NOT NULL, status ENUM('NEW','ACCEPTED','PREPARING','DELAYED','READY','SERVED','CANCELLED') NOT NULL DEFAULT 'NEW', delayed_from_status ENUM('NEW','ACCEPTED','PREPARING') NULL, delay_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 0, delay_reason VARCHAR(255) NULL, accepted_at DATETIME NULL, preparing_at DATETIME NULL, ready_at DATETIME NULL, served_at DATETIME NULL, cancelled_at DATETIME NULL, delayed_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_kitchen_order_public (public_id), UNIQUE KEY uq_kitchen_ticket_number (ticket_number), UNIQUE KEY uq_kitchen_order_route (order_id,kitchen_id,station_id), INDEX idx_kds_queue (kitchen_id,station_id,status,created_at), INDEX idx_kitchen_order_order (order_id,status), CONSTRAINT fk_kitchen_orders_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, CONSTRAINT fk_kitchen_orders_kitchen FOREIGN KEY (kitchen_id) REFERENCES kitchens(id) ON DELETE RESTRICT, CONSTRAINT fk_kitchen_orders_station FOREIGN KEY (station_id) REFERENCES kitchen_stations(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS kitchen_order_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, kitchen_order_id BIGINT UNSIGNED NOT NULL, order_item_id BIGINT UNSIGNED NOT NULL, quantity SMALLINT UNSIGNED NOT NULL, status ENUM('NEW','ACCEPTED','PREPARING','READY','SERVED','CANCELLED') NOT NULL DEFAULT 'NEW', requested_time DATETIME NULL, started_at DATETIME NULL, ready_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_kitchen_order_item (order_item_id), INDEX idx_kitchen_items_ticket (kitchen_order_id,status), CONSTRAINT fk_kitchen_order_items_ticket FOREIGN KEY (kitchen_order_id) REFERENCES kitchen_orders(id) ON DELETE CASCADE, CONSTRAINT fk_kitchen_order_items_order_item FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS kitchen_status_history ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, kitchen_order_id BIGINT UNSIGNED NOT NULL, old_status VARCHAR(40) NULL, new_status VARCHAR(40) NOT NULL, user_id BIGINT UNSIGNED NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_kitchen_history (kitchen_order_id,created_at), CONSTRAINT fk_kitchen_history_ticket FOREIGN KEY (kitchen_order_id) REFERENCES kitchen_orders(id) ON DELETE CASCADE, CONSTRAINT fk_kitchen_history_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS notifications ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, customer_session_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL, type VARCHAR(60) NOT NULL, channel ENUM('IN_APP','KDS','EMAIL','SMS','WHATSAPP','PUSH') NOT NULL DEFAULT 'IN_APP', title VARCHAR(180) NOT NULL, message VARCHAR(500) NOT NULL, data_json JSON NULL, status ENUM('PENDING','DELIVERED','READ','FAILED') NOT NULL DEFAULT 'DELIVERED', read_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_notification_public (public_id), INDEX idx_notification_customer (customer_session_id,status,created_at), INDEX idx_notification_user (user_id,status,created_at), INDEX idx_notification_hotel_type (hotel_id,type,created_at), CONSTRAINT fk_notifications_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE, CONSTRAINT fk_notifications_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE CASCADE, CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO permissions (code,name) VALUES ('kds.view','View kitchen display system'), ('kds.manage','Manage kitchen ticket status'), ('kitchen.manage','Manage kitchen configuration') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('SUPER_ADMIN','HOTEL_ADMIN') AND p.code IN ('kds.view','kds.manage','kitchen.manage'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code='KITCHEN_MANAGER' AND p.code IN ('kds.view','kds.manage','kitchen.manage','orders.view'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code='KITCHEN_STAFF' AND p.code IN ('kds.view','kds.manage','orders.view'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('RESTAURANT_MANAGER','ROOM_SERVICE_MANAGER','READ_ONLY_MANAGER') AND p.code='kds.view'; -- ============================================================================ -- MIGRATION: 009_room_service.sql -- ============================================================================ CREATE TABLE IF NOT EXISTS room_service_orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, order_id BIGINT UNSIGNED NOT NULL, room_session_id BIGINT UNSIGNED NOT NULL, delivery_type ENUM('ASAP','SCHEDULED') NOT NULL DEFAULT 'ASAP', requested_delivery_at DATETIME NULL, estimated_delivery_at DATETIME NULL, status ENUM('QUEUED','KITCHEN','READY_FOR_DISPATCH','ASSIGNED','DISPATCHED','DELIVERED','CANCELLED') NOT NULL DEFAULT 'QUEUED', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_room_service_public (public_id), UNIQUE KEY uq_room_service_order (order_id), INDEX idx_room_service_queue (status,requested_delivery_at,estimated_delivery_at,created_at), INDEX idx_room_service_room_session (room_session_id,created_at), CONSTRAINT fk_room_service_orders_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, CONSTRAINT fk_room_service_orders_room_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS room_service_deliveries ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, room_service_order_id BIGINT UNSIGNED NOT NULL, assigned_user_id BIGINT UNSIGNED NULL, status ENUM('ASSIGNED','DISPATCHED','DELIVERED','CANCELLED') NOT NULL DEFAULT 'ASSIGNED', estimated_delivery_at DATETIME NULL, delay_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 0, delay_reason VARCHAR(255) NULL, assigned_at DATETIME NULL, dispatched_at DATETIME NULL, delivered_at DATETIME NULL, cancelled_at DATETIME NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_room_service_delivery_public (public_id), UNIQUE KEY uq_room_service_delivery_order (room_service_order_id), INDEX idx_room_service_delivery_staff (assigned_user_id,status,created_at), INDEX idx_room_service_delivery_status (status,estimated_delivery_at), CONSTRAINT fk_room_service_delivery_order FOREIGN KEY (room_service_order_id) REFERENCES room_service_orders(id) ON DELETE CASCADE, CONSTRAINT fk_room_service_delivery_user FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS room_service_status_history ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, room_service_order_id BIGINT UNSIGNED NOT NULL, old_status VARCHAR(40) NULL, new_status VARCHAR(40) NOT NULL, user_id BIGINT UNSIGNED NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_room_service_history (room_service_order_id,created_at), CONSTRAINT fk_room_service_history_order FOREIGN KEY (room_service_order_id) REFERENCES room_service_orders(id) ON DELETE CASCADE, CONSTRAINT fk_room_service_history_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO permissions (code,name) VALUES ('room_service.view','View room-service delivery operations'), ('room_service.manage','Manage room-service delivery operations') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('SUPER_ADMIN','HOTEL_ADMIN','ROOM_SERVICE_MANAGER') AND p.code IN ('room_service.view','room_service.manage'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('ROOM_SERVICE_STAFF','READ_ONLY_MANAGER') AND p.code='room_service.view'; INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code='ROOM_SERVICE_STAFF' AND p.code='room_service.manage'; INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'room_service_delivery_minutes','10','INTEGER' FROM hotels ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'room_service_schedule_lead_minutes','30','INTEGER' FROM hotels ON DUPLICATE KEY UPDATE setting_value=setting_value; -- ============================================================================ -- MIGRATION: 010_reservations.sql -- ============================================================================ CREATE TABLE IF NOT EXISTS reservation_settings ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, restaurant_id BIGINT UNSIGNED NOT NULL, enabled TINYINT(1) NOT NULL DEFAULT 1, slot_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 30, duration_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 90, turnover_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 15, hold_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 5, minimum_party_size SMALLINT UNSIGNED NOT NULL DEFAULT 1, maximum_party_size SMALLINT UNSIGNED NOT NULL DEFAULT 20, advance_days SMALLINT UNSIGNED NOT NULL DEFAULT 30, max_combined_tables TINYINT UNSIGNED NOT NULL DEFAULT 3, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_reservation_settings_restaurant (restaurant_id), CONSTRAINT fk_reservation_settings_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE, CONSTRAINT chk_reservation_settings_slot CHECK (slot_minutes BETWEEN 5 AND 240), CONSTRAINT chk_reservation_settings_duration CHECK (duration_minutes BETWEEN 15 AND 720), CONSTRAINT chk_reservation_settings_party CHECK (minimum_party_size BETWEEN 1 AND maximum_party_size), CONSTRAINT chk_reservation_settings_combined CHECK (max_combined_tables BETWEEN 1 AND 6) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS restaurant_reservations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, reservation_number VARCHAR(40) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, restaurant_id BIGINT UNSIGNED NOT NULL, room_session_id BIGINT UNSIGNED NULL, customer_session_id BIGINT UNSIGNED NULL, guest_name VARCHAR(160) NULL, guest_phone VARCHAR(50) NULL, guest_email VARCHAR(190) NULL, guest_count SMALLINT UNSIGNED NOT NULL, start_at DATETIME NOT NULL, end_at DATETIME NOT NULL, status ENUM('HOLD','PENDING','CONFIRMED','SEATED','COMPLETED','CANCELLED','NO_SHOW','EXPIRED') NOT NULL DEFAULT 'CONFIRMED', source ENUM('ROOM_QR','PUBLIC','STAFF','API') NOT NULL DEFAULT 'ROOM_QR', notes VARCHAR(500) NULL, created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_reservation_public_id (public_id), UNIQUE KEY uq_reservation_number (hotel_id,reservation_number), INDEX idx_reservation_restaurant_window (restaurant_id,status,start_at,end_at), INDEX idx_reservation_customer (customer_session_id,created_at), INDEX idx_reservation_room (room_session_id,created_at), CONSTRAINT fk_reservation_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE RESTRICT, CONSTRAINT fk_reservation_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE RESTRICT, CONSTRAINT fk_reservation_room_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE SET NULL, CONSTRAINT fk_reservation_customer_session FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL, CONSTRAINT fk_reservation_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT chk_reservation_guest_count CHECK (guest_count BETWEEN 1 AND 200), CONSTRAINT chk_reservation_window CHECK (end_at > start_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS reservation_holds ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, restaurant_id BIGINT UNSIGNED NOT NULL, room_session_id BIGINT UNSIGNED NULL, customer_session_id BIGINT UNSIGNED NULL, guest_count SMALLINT UNSIGNED NOT NULL, start_at DATETIME NOT NULL, end_at DATETIME NOT NULL, expires_at DATETIME NOT NULL, status ENUM('ACTIVE','CONFIRMED','EXPIRED','CANCELLED') NOT NULL DEFAULT 'ACTIVE', confirmed_reservation_id BIGINT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_reservation_hold_public (public_id), UNIQUE KEY uq_reservation_hold_confirmed (confirmed_reservation_id), INDEX idx_reservation_hold_window (restaurant_id,status,start_at,end_at,expires_at), INDEX idx_reservation_hold_customer (customer_session_id,status,expires_at), CONSTRAINT fk_reservation_hold_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE, CONSTRAINT fk_reservation_hold_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE, CONSTRAINT fk_reservation_hold_room_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE CASCADE, CONSTRAINT fk_reservation_hold_customer_session FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE CASCADE, CONSTRAINT fk_reservation_hold_confirmed FOREIGN KEY (confirmed_reservation_id) REFERENCES restaurant_reservations(id) ON DELETE SET NULL, CONSTRAINT chk_reservation_hold_window CHECK (end_at > start_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS reservation_hold_tables ( reservation_hold_id BIGINT UNSIGNED NOT NULL, table_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (reservation_hold_id,table_id), INDEX idx_reservation_hold_table_lookup (table_id,reservation_hold_id), CONSTRAINT fk_reservation_hold_tables_hold FOREIGN KEY (reservation_hold_id) REFERENCES reservation_holds(id) ON DELETE CASCADE, CONSTRAINT fk_reservation_hold_tables_table FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS reservation_tables ( reservation_id BIGINT UNSIGNED NOT NULL, table_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (reservation_id,table_id), INDEX idx_reservation_table_lookup (table_id,reservation_id), CONSTRAINT fk_reservation_tables_reservation FOREIGN KEY (reservation_id) REFERENCES restaurant_reservations(id) ON DELETE CASCADE, CONSTRAINT fk_reservation_tables_table FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS reservation_status_history ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, reservation_id BIGINT UNSIGNED NOT NULL, old_status VARCHAR(40) NULL, new_status VARCHAR(40) NOT NULL, user_id BIGINT UNSIGNED NULL, customer_session_id BIGINT UNSIGNED NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_reservation_history (reservation_id,created_at), CONSTRAINT fk_reservation_history_reservation FOREIGN KEY (reservation_id) REFERENCES restaurant_reservations(id) ON DELETE CASCADE, CONSTRAINT fk_reservation_history_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_reservation_history_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET @reservation_fk_exists := (SELECT COUNT(*) FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=DATABASE() AND CONSTRAINT_NAME='fk_orders_reservation'); SET @reservation_fk_sql := IF(@reservation_fk_exists=0,'ALTER TABLE orders ADD CONSTRAINT fk_orders_reservation FOREIGN KEY (reservation_id) REFERENCES restaurant_reservations(id) ON DELETE SET NULL','SELECT 1'); PREPARE reservation_fk_stmt FROM @reservation_fk_sql; EXECUTE reservation_fk_stmt; DEALLOCATE PREPARE reservation_fk_stmt; INSERT INTO reservation_settings (restaurant_id,enabled,slot_minutes,duration_minutes,turnover_minutes,hold_minutes,minimum_party_size,maximum_party_size,advance_days,max_combined_tables) SELECT r.id,1,30,90,15,5,1,20,30,3 FROM restaurants r WHERE r.type IN ('RESTAURANT','CAFE') ON DUPLICATE KEY UPDATE restaurant_id=VALUES(restaurant_id); INSERT INTO permissions (code,name) VALUES ('reservations.view','View restaurant reservations'), ('reservations.manage','Manage restaurant reservations'), ('reservations.settings','Configure reservation settings') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('SUPER_ADMIN','HOTEL_ADMIN','RESTAURANT_MANAGER') AND p.code IN ('reservations.view','reservations.manage','reservations.settings'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('WAITER','ROOM_SERVICE_MANAGER','READ_ONLY_MANAGER') AND p.code='reservations.view'; INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code='WAITER' AND p.code='reservations.manage'; -- ============================================================================ -- MIGRATION: 011_billing_payments.sql -- ============================================================================ CREATE TABLE IF NOT EXISTS bills ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, bill_number VARCHAR(40) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, restaurant_id BIGINT UNSIGNED NULL, table_session_id BIGINT UNSIGNED NULL, room_session_id BIGINT UNSIGNED NULL, owner_customer_session_id BIGINT UNSIGNED NULL, split_mode ENUM('UNSPLIT','CUSTOMER','EQUAL','ITEM','SELECTED') NOT NULL DEFAULT 'UNSPLIT', currency CHAR(3) NOT NULL, subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00, discount DECIMAL(12,2) NOT NULL DEFAULT 0.00, tax DECIMAL(12,2) NOT NULL DEFAULT 0.00, total DECIMAL(12,2) NOT NULL DEFAULT 0.00, paid_total DECIMAL(12,2) NOT NULL DEFAULT 0.00, balance_due DECIMAL(12,2) NOT NULL DEFAULT 0.00, status ENUM('OPEN','PARTIALLY_PAID','PAID','VOID') NOT NULL DEFAULT 'OPEN', opened_at DATETIME NOT NULL, closed_at DATETIME NULL, voided_at DATETIME NULL, created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_bill_public (public_id), UNIQUE KEY uq_bill_number (hotel_id,bill_number), INDEX idx_bill_hotel_status (hotel_id,status,created_at), INDEX idx_bill_table (table_session_id,status), INDEX idx_bill_room (room_session_id,status), CONSTRAINT fk_bill_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE RESTRICT, CONSTRAINT fk_bill_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE SET NULL, CONSTRAINT fk_bill_table_session FOREIGN KEY (table_session_id) REFERENCES table_sessions(id) ON DELETE SET NULL, CONSTRAINT fk_bill_room_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE SET NULL, CONSTRAINT fk_bill_owner_customer FOREIGN KEY (owner_customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL, CONSTRAINT fk_bill_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, -- MariaDB compatibility: bill context is enforced by BillingService. -- Some MariaDB releases reject CHECK constraints that reference FK columns -- using ON DELETE SET NULL (ERROR 1901), so chk_bill_context is intentionally omitted. CONSTRAINT chk_bill_money CHECK (subtotal>=0 AND discount>=0 AND tax>=0 AND total>=0 AND paid_total>=0 AND balance_due>=0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS bill_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, bill_id BIGINT UNSIGNED NOT NULL, order_id BIGINT UNSIGNED NOT NULL, order_item_id BIGINT UNSIGNED NOT NULL, customer_session_id BIGINT UNSIGNED NULL, description_snapshot VARCHAR(500) NOT NULL, quantity SMALLINT UNSIGNED NOT NULL, unit_subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00, unit_tax DECIMAL(12,2) NOT NULL DEFAULT 0.00, unit_total DECIMAL(12,2) NOT NULL DEFAULT 0.00, subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00, tax DECIMAL(12,2) NOT NULL DEFAULT 0.00, total DECIMAL(12,2) NOT NULL DEFAULT 0.00, status ENUM('OPEN','VOID') NOT NULL DEFAULT 'OPEN', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_bill_item_public (public_id), UNIQUE KEY uq_bill_order_item (bill_id,order_item_id), INDEX idx_bill_items_bill (bill_id,status,id), INDEX idx_bill_items_customer (customer_session_id,bill_id), CONSTRAINT fk_bill_items_bill FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE CASCADE, CONSTRAINT fk_bill_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT, CONSTRAINT fk_bill_items_order_item FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE RESTRICT, CONSTRAINT fk_bill_items_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL, CONSTRAINT chk_bill_item_money CHECK (subtotal>=0 AND tax>=0 AND total>=0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS bill_allocations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, bill_id BIGINT UNSIGNED NOT NULL, bill_item_id BIGINT UNSIGNED NULL, customer_session_id BIGINT UNSIGNED NULL, allocation_group CHAR(26) NOT NULL, allocation_type ENUM('ENTIRE_BILL','CUSTOMER_ITEMS','SELECTED_ITEMS','EQUAL_SHARE','ITEM_SHARE') NOT NULL, share_index SMALLINT UNSIGNED NULL, share_count SMALLINT UNSIGNED NULL, amount DECIMAL(12,2) NOT NULL, paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, status ENUM('OPEN','PARTIALLY_PAID','PAID','CANCELLED') NOT NULL DEFAULT 'OPEN', created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_bill_allocation_public (public_id), INDEX idx_bill_allocations_bill (bill_id,status,allocation_type), INDEX idx_bill_allocations_customer (customer_session_id,bill_id,status), INDEX idx_bill_allocations_item (bill_item_id,status), CONSTRAINT fk_bill_alloc_bill FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE CASCADE, CONSTRAINT fk_bill_alloc_item FOREIGN KEY (bill_item_id) REFERENCES bill_items(id) ON DELETE CASCADE, CONSTRAINT fk_bill_alloc_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL, CONSTRAINT fk_bill_alloc_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT chk_bill_alloc_amount CHECK (amount>0 AND paid_amount>=0 AND paid_amount<=amount) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS payments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, payment_number VARCHAR(50) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, bill_id BIGINT UNSIGNED NOT NULL, customer_session_id BIGINT UNSIGNED NULL, method ENUM('CASH','CARD','ONLINE','ROOM_CHARGE') NOT NULL, amount DECIMAL(12,2) NOT NULL, currency CHAR(3) NOT NULL, status ENUM('PENDING','AUTHORIZED','PAID','FAILED','CANCELLED','REFUNDED','PARTIALLY_REFUNDED') NOT NULL DEFAULT 'PENDING', provider VARCHAR(100) NULL, provider_reference VARCHAR(190) NULL, idempotency_key_hash CHAR(64) NOT NULL, request_hash CHAR(64) NOT NULL, created_by BIGINT UNSIGNED NULL, authorized_at DATETIME NULL, paid_at DATETIME NULL, failed_at DATETIME NULL, cancelled_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_payment_public (public_id), UNIQUE KEY uq_payment_number (hotel_id,payment_number), UNIQUE KEY uq_payment_idempotency (hotel_id,idempotency_key_hash), INDEX idx_payment_bill (bill_id,status,created_at), INDEX idx_payment_hotel_status (hotel_id,status,created_at), CONSTRAINT fk_payment_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE RESTRICT, CONSTRAINT fk_payment_bill FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE RESTRICT, CONSTRAINT fk_payment_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL, CONSTRAINT fk_payment_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT chk_payment_amount CHECK (amount>0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS payment_transactions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, payment_id BIGINT UNSIGNED NOT NULL, transaction_type ENUM('CREATE','AUTHORIZE','CAPTURE','VERIFY','VOID','REFUND','ROOM_CHARGE_POST') NOT NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, status ENUM('PENDING','SUCCESS','FAILED') NOT NULL, provider VARCHAR(100) NULL, provider_reference VARCHAR(190) NULL, request_json JSON NULL, response_json JSON NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_payment_tx_public (public_id), INDEX idx_payment_tx_payment (payment_id,created_at), CONSTRAINT fk_payment_tx_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS payment_allocations ( payment_id BIGINT UNSIGNED NOT NULL, bill_allocation_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(12,2) NOT NULL, PRIMARY KEY (payment_id,bill_allocation_id), CONSTRAINT fk_payment_alloc_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE, CONSTRAINT fk_payment_alloc_bill_alloc FOREIGN KEY (bill_allocation_id) REFERENCES bill_allocations(id) ON DELETE RESTRICT, CONSTRAINT chk_payment_alloc_amount CHECK (amount>0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS room_charge_credentials ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, room_session_id BIGINT UNSIGNED NOT NULL, pin_hash VARCHAR(255) NULL, status ENUM('ACTIVE','REVOKED') NOT NULL DEFAULT 'ACTIVE', created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_room_charge_credential_session (room_session_id), CONSTRAINT fk_room_charge_credential_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE CASCADE, CONSTRAINT fk_room_charge_credential_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS room_charge_requests ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, payment_id BIGINT UNSIGNED NOT NULL, room_session_id BIGINT UNSIGNED NOT NULL, room_number_snapshot VARCHAR(50) NOT NULL, verification_method ENUM('ROOM_GUEST','ROOM_PIN','OTP','PMS','STAFF_APPROVAL') NOT NULL, verification_status ENUM('PENDING','VERIFIED','REJECTED','EXPIRED') NOT NULL DEFAULT 'PENDING', verification_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0, verified_by BIGINT UNSIGNED NULL, verification_notes VARCHAR(500) NULL, pms_reference VARCHAR(190) NULL, expires_at DATETIME NULL, verified_at DATETIME NULL, rejected_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_room_charge_request_public (public_id), UNIQUE KEY uq_room_charge_request_payment (payment_id), INDEX idx_room_charge_pending (verification_status,created_at), INDEX idx_room_charge_session (room_session_id,created_at), CONSTRAINT fk_room_charge_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE, CONSTRAINT fk_room_charge_session FOREIGN KEY (room_session_id) REFERENCES room_sessions(id) ON DELETE RESTRICT, CONSTRAINT fk_room_charge_verified_by FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS bill_status_history ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, bill_id BIGINT UNSIGNED NOT NULL, old_status VARCHAR(40) NULL, new_status VARCHAR(40) NOT NULL, user_id BIGINT UNSIGNED NULL, customer_session_id BIGINT UNSIGNED NULL, notes VARCHAR(500) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_bill_history (bill_id,created_at), CONSTRAINT fk_bill_history_bill FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE CASCADE, CONSTRAINT fk_bill_history_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_bill_history_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO permissions (code,name) VALUES ('billing.view','View bills and bill allocations'), ('billing.manage','Manage bills and split allocations'), ('payments.view','View payment records'), ('payments.manage','Record and manage payments'), ('room_charge.approve','Approve or reject room-charge requests') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('SUPER_ADMIN','HOTEL_ADMIN') AND p.code IN ('billing.view','billing.manage','payments.view','payments.manage','room_charge.approve'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('CASHIER','BILLING_STAFF') AND p.code IN ('billing.view','billing.manage','payments.view','payments.manage','room_charge.approve'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('RESTAURANT_MANAGER','ROOM_SERVICE_MANAGER') AND p.code IN ('billing.view','payments.view'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code='READ_ONLY_MANAGER' AND p.code IN ('billing.view','payments.view'); INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'room_charge_verification_method','STAFF_APPROVAL','STRING' FROM hotels ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'online_payment_enabled','0','BOOLEAN' FROM hotels ON DUPLICATE KEY UPDATE setting_value=setting_value; -- ============================================================================ -- MIGRATION: 012_integrations.sql -- ============================================================================ CREATE TABLE IF NOT EXISTS integration_connections ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, integration_type ENUM('PMS','POS','ERP') NOT NULL, provider VARCHAR(100) NOT NULL, adapter_key VARCHAR(100) NOT NULL, display_name VARCHAR(160) NOT NULL, status ENUM('ACTIVE','DISABLED','ERROR') NOT NULL DEFAULT 'DISABLED', config_json JSON NULL, last_health_status ENUM('UNKNOWN','OK','ERROR') NOT NULL DEFAULT 'UNKNOWN', last_health_at DATETIME NULL, last_error VARCHAR(1000) NULL, created_by BIGINT UNSIGNED NULL, updated_by BIGINT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_integration_connection_public (public_id), UNIQUE KEY uq_integration_connection_type (hotel_id,integration_type), INDEX idx_integration_connection_status (hotel_id,status,integration_type), CONSTRAINT fk_integration_connection_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE, CONSTRAINT fk_integration_connection_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_integration_connection_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS integration_events ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, connection_id BIGINT UNSIGNED NULL, integration_type ENUM('PMS','POS','ERP') NOT NULL, direction ENUM('INBOUND','OUTBOUND','INTERNAL') NOT NULL DEFAULT 'INTERNAL', event_type VARCHAR(120) NOT NULL, entity_type VARCHAR(100) NULL, entity_public_id VARCHAR(100) NULL, correlation_id CHAR(26) NOT NULL, status ENUM('PENDING','SUCCESS','FAILED','SKIPPED') NOT NULL DEFAULT 'PENDING', request_json JSON NULL, response_json JSON NULL, error_message VARCHAR(1500) NULL, attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0, started_at DATETIME NOT NULL, completed_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_integration_event_public (public_id), INDEX idx_integration_events_hotel (hotel_id,integration_type,status,created_at), INDEX idx_integration_events_entity (entity_type,entity_public_id,created_at), INDEX idx_integration_events_correlation (correlation_id), CONSTRAINT fk_integration_event_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE, CONSTRAINT fk_integration_event_connection FOREIGN KEY (connection_id) REFERENCES integration_connections(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS integration_entity_links ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, hotel_id BIGINT UNSIGNED NOT NULL, integration_type ENUM('PMS','POS','ERP') NOT NULL, entity_type VARCHAR(100) NOT NULL, local_public_id VARCHAR(100) NOT NULL, external_id VARCHAR(190) NOT NULL, external_version VARCHAR(100) NULL, synced_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_integration_entity_local (hotel_id,integration_type,entity_type,local_public_id), UNIQUE KEY uq_integration_entity_external (hotel_id,integration_type,entity_type,external_id), CONSTRAINT fk_integration_entity_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS integration_outbox ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, integration_type ENUM('POS','ERP') NOT NULL, event_type VARCHAR(120) NOT NULL, entity_type VARCHAR(100) NOT NULL, entity_public_id VARCHAR(100) NOT NULL, payload_json JSON NOT NULL, status ENUM('PENDING','PROCESSING','SENT','FAILED','DEAD') NOT NULL DEFAULT 'PENDING', attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0, available_at DATETIME NOT NULL, locked_at DATETIME NULL, last_error VARCHAR(1500) NULL, sent_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_integration_outbox_public (public_id), UNIQUE KEY uq_integration_outbox_event (hotel_id,integration_type,event_type,entity_type,entity_public_id), INDEX idx_integration_outbox_work (integration_type,status,available_at,id), CONSTRAINT fk_integration_outbox_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS mock_pms_guests ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, room_id BIGINT UNSIGNED NOT NULL, external_guest_id VARCHAR(100) NOT NULL, guest_name VARCHAR(160) NOT NULL, verification_hash VARCHAR(255) NOT NULL, checkin_at DATETIME NOT NULL, expected_checkout_at DATETIME NULL, checkout_at DATETIME NULL, status ENUM('CHECKED_IN','CHECKED_OUT') NOT NULL DEFAULT 'CHECKED_IN', room_charge_allowed TINYINT(1) NOT NULL DEFAULT 1, credit_limit DECIMAL(12,2) NULL, posted_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, notes VARCHAR(500) NULL, created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_mock_pms_guest_public (public_id), UNIQUE KEY uq_mock_pms_guest_external (hotel_id,external_guest_id), INDEX idx_mock_pms_room_status (room_id,status,checkin_at), CONSTRAINT fk_mock_pms_guest_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE, CONSTRAINT fk_mock_pms_guest_room FOREIGN KEY (room_id) REFERENCES hotel_rooms(id) ON DELETE CASCADE, CONSTRAINT fk_mock_pms_guest_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT chk_mock_pms_posted CHECK (posted_amount>=0), CONSTRAINT chk_mock_pms_credit CHECK (credit_limit IS NULL OR credit_limit>=0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS mock_pms_room_charges ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, guest_id BIGINT UNSIGNED NOT NULL, payment_id BIGINT UNSIGNED NULL, external_reference VARCHAR(190) NOT NULL, amount DECIMAL(12,2) NOT NULL, currency CHAR(3) NOT NULL, description VARCHAR(500) NOT NULL, posted_at DATETIME NOT NULL, reversed_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_mock_pms_charge_public (public_id), UNIQUE KEY uq_mock_pms_charge_ref (hotel_id,external_reference), UNIQUE KEY uq_mock_pms_payment (payment_id), CONSTRAINT fk_mock_pms_charge_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE, CONSTRAINT fk_mock_pms_charge_guest FOREIGN KEY (guest_id) REFERENCES mock_pms_guests(id) ON DELETE RESTRICT, CONSTRAINT fk_mock_pms_charge_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL, CONSTRAINT chk_mock_pms_charge_amount CHECK (amount>0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO permissions (code,name) VALUES ('integrations.view','View PMS, POS and ERP integration status and logs'), ('integrations.manage','Configure and operate PMS, POS and ERP integrations') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('SUPER_ADMIN','HOTEL_ADMIN','IT_ADMINISTRATOR') AND p.code IN ('integrations.view','integrations.manage'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code='READ_ONLY_MANAGER' AND p.code='integrations.view'; INSERT INTO integration_connections (public_id,hotel_id,integration_type,provider,adapter_key,display_name,status,config_json) SELECT LEFT(REPLACE(UUID(),'-',''),26),id,'PMS','MOCK','mock','Mock PMS','ACTIVE',JSON_OBJECT('mode','local_database') FROM hotels ON DUPLICATE KEY UPDATE provider=provider; INSERT INTO integration_connections (public_id,hotel_id,integration_type,provider,adapter_key,display_name,status,config_json) SELECT LEFT(REPLACE(UUID(),'-',''),26),id,'POS','NONE','null','POS Integration','DISABLED',JSON_OBJECT() FROM hotels ON DUPLICATE KEY UPDATE provider=provider; INSERT INTO integration_connections (public_id,hotel_id,integration_type,provider,adapter_key,display_name,status,config_json) SELECT LEFT(REPLACE(UUID(),'-',''),26),id,'ERP','NONE','null','ERP Integration','DISABLED',JSON_OBJECT() FROM hotels ON DUPLICATE KEY UPDATE provider=provider; INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'pms_adapter','mock','STRING' FROM hotels ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'pos_adapter','null','STRING' FROM hotels ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'erp_adapter','null','STRING' FROM hotels ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'pms_auto_room_session_sync','1','BOOLEAN' FROM hotels ON DUPLICATE KEY UPDATE setting_value=setting_value; -- ============================================================================ -- MIGRATION: 013_reporting_analytics.sql -- ============================================================================ CREATE TABLE IF NOT EXISTS order_item_reporting_dimensions ( order_item_id BIGINT UNSIGNED PRIMARY KEY, category_id BIGINT UNSIGNED NULL, category_name_snapshot VARCHAR(160) NULL, captured_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_order_item_reporting_item FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE, CONSTRAINT fk_order_item_reporting_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO order_item_reporting_dimensions (order_item_id,category_id,category_name_snapshot) SELECT oi.id,mi.category_id,c.name FROM order_items oi LEFT JOIN menu_items mi ON mi.id=oi.menu_item_id LEFT JOIN categories c ON c.id=mi.category_id ON DUPLICATE KEY UPDATE order_item_id=VALUES(order_item_id); CREATE TABLE IF NOT EXISTS report_daily_metrics ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, hotel_id BIGINT UNSIGNED NOT NULL, metric_date DATE NOT NULL, orders_count INT UNSIGNED NOT NULL DEFAULT 0, served_orders_count INT UNSIGNED NOT NULL DEFAULT 0, cancelled_orders_count INT UNSIGNED NOT NULL DEFAULT 0, order_sales DECIMAL(14,2) NOT NULL DEFAULT 0.00, order_tax DECIMAL(14,2) NOT NULL DEFAULT 0.00, paid_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00, reservations_count INT UNSIGNED NOT NULL DEFAULT 0, room_service_orders_count INT UNSIGNED NOT NULL DEFAULT 0, delayed_kitchen_tickets INT UNSIGNED NOT NULL DEFAULT 0, avg_preparation_minutes DECIMAL(10,2) NULL, refreshed_at DATETIME NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_report_daily_hotel_date (hotel_id,metric_date), INDEX idx_report_daily_date (metric_date,hotel_id), CONSTRAINT fk_report_daily_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO permissions (code,name) VALUES ('reports.view','View operational, sales, tax and performance reports'), ('reports.export','Export reports to CSV'), ('audit.view','View audit history and security activity') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('SUPER_ADMIN','HOTEL_ADMIN') AND p.code IN ('reports.view','reports.export','audit.view'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code='READ_ONLY_MANAGER' AND p.code IN ('reports.view','audit.view'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('CASHIER','BILLING_STAFF') AND p.code='reports.view'; INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code='IT_ADMINISTRATOR' AND p.code='audit.view'; -- ============================================================================ -- MIGRATION: 014_runtime_operations.sql -- ============================================================================ CREATE TABLE IF NOT EXISTS runtime_job_runs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, job_name VARCHAR(120) NOT NULL, status ENUM('RUNNING','SUCCESS','FAILED') NOT NULL DEFAULT 'RUNNING', started_at DATETIME NOT NULL, finished_at DATETIME NULL, duration_ms INT UNSIGNED NULL, summary VARCHAR(1000) NULL, error_message VARCHAR(1000) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_runtime_job_name_started (job_name,started_at), INDEX idx_runtime_job_status_started (status,started_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================ -- MIGRATION: 015_guest_engagement.sql -- ============================================================================ -- Phase 11: guest waiting experience, promotions, events and engagement analytics SET @phase11_has_serving_time := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='order_items' AND COLUMN_NAME='serving_time'); SET @phase11_add_serving_time := IF(@phase11_has_serving_time=0,'ALTER TABLE order_items ADD COLUMN serving_time SMALLINT UNSIGNED NULL AFTER preparation_time','SELECT 1'); PREPARE phase11_stmt FROM @phase11_add_serving_time; EXECUTE phase11_stmt; DEALLOCATE PREPARE phase11_stmt; UPDATE order_items oi JOIN menu_items mi ON mi.id=oi.menu_item_id SET oi.serving_time=mi.serving_time WHERE oi.serving_time IS NULL; CREATE TABLE IF NOT EXISTS promotions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, restaurant_id BIGINT UNSIGNED NULL, menu_item_id BIGINT UNSIGNED NULL, title VARCHAR(180) NOT NULL, subtitle VARCHAR(220) NULL, description TEXT NULL, promotion_type ENUM('OFFER','DISCOUNT','SPECIAL_DISH','WEEKEND_SPECIAL','WEEKDAY_SPECIAL') NOT NULL DEFAULT 'OFFER', discount_type ENUM('NONE','PERCENT','FIXED') NOT NULL DEFAULT 'NONE', discount_value DECIMAL(12,2) NOT NULL DEFAULT 0.00, audience ENUM('ALL','ROOM_GUESTS','DINE_IN','ROOM_SERVICE') NOT NULL DEFAULT 'ALL', start_at DATETIME NULL, end_at DATETIME NULL, weekday_mask TINYINT UNSIGNED NOT NULL DEFAULT 0, time_from TIME NULL, time_to TIME NULL, minimum_order_total DECIMAL(12,2) NULL, priority SMALLINT NOT NULL DEFAULT 100, status ENUM('DRAFT','ACTIVE','INACTIVE') NOT NULL DEFAULT 'DRAFT', created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_promotion_public (public_id), INDEX idx_promotion_active (hotel_id,status,start_at,end_at,priority), INDEX idx_promotion_restaurant (restaurant_id,status), CONSTRAINT fk_promotion_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE, CONSTRAINT fk_promotion_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE SET NULL, CONSTRAINT fk_promotion_menu_item FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE SET NULL, CONSTRAINT fk_promotion_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS hotel_events ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, restaurant_id BIGINT UNSIGNED NULL, title VARCHAR(180) NOT NULL, description TEXT NULL, location VARCHAR(220) NULL, starts_at DATETIME NOT NULL, ends_at DATETIME NULL, booking_opens_at DATETIME NULL, booking_closes_at DATETIME NULL, priority SMALLINT NOT NULL DEFAULT 100, status ENUM('DRAFT','ACTIVE','CANCELLED','COMPLETED') NOT NULL DEFAULT 'DRAFT', created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_hotel_event_public (public_id), INDEX idx_hotel_event_upcoming (hotel_id,status,starts_at,priority), INDEX idx_hotel_event_restaurant (restaurant_id,starts_at), CONSTRAINT fk_hotel_event_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE, CONSTRAINT fk_hotel_event_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE SET NULL, CONSTRAINT fk_hotel_event_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS engagement_cards ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, promotion_id BIGINT UNSIGNED NULL, event_id BIGINT UNSIGNED NULL, restaurant_id BIGINT UNSIGNED NULL, title VARCHAR(180) NULL, body VARCHAR(500) NULL, audience ENUM('ALL','ROOM_GUESTS','DINE_IN','ROOM_SERVICE') NOT NULL DEFAULT 'ALL', min_wait_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 0, max_wait_minutes SMALLINT UNSIGNED NULL, cta_label VARCHAR(80) NULL, cta_type ENUM('NONE','MENU_ITEM','RESERVATION','LOCAL_URL') NOT NULL DEFAULT 'NONE', cta_url VARCHAR(500) NULL, display_order SMALLINT NOT NULL DEFAULT 100, status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_engagement_card_public (public_id), INDEX idx_engagement_card_active (hotel_id,status,display_order), CONSTRAINT fk_engagement_card_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE, CONSTRAINT fk_engagement_card_promotion FOREIGN KEY (promotion_id) REFERENCES promotions(id) ON DELETE CASCADE, CONSTRAINT fk_engagement_card_event FOREIGN KEY (event_id) REFERENCES hotel_events(id) ON DELETE CASCADE, CONSTRAINT fk_engagement_card_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS engagement_impressions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, card_id BIGINT UNSIGNED NOT NULL, order_id BIGINT UNSIGNED NOT NULL, customer_session_id BIGINT UNSIGNED NOT NULL, remaining_minutes SMALLINT UNSIGNED NULL, first_shown_at DATETIME NOT NULL, last_shown_at DATETIME NOT NULL, show_count INT UNSIGNED NOT NULL DEFAULT 1, UNIQUE KEY uq_engagement_impression_public (public_id), UNIQUE KEY uq_engagement_impression_context (card_id,order_id,customer_session_id), INDEX idx_engagement_impression_hotel (hotel_id,first_shown_at), CONSTRAINT fk_engagement_impression_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE, CONSTRAINT fk_engagement_impression_card FOREIGN KEY (card_id) REFERENCES engagement_cards(id) ON DELETE CASCADE, CONSTRAINT fk_engagement_impression_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, CONSTRAINT fk_engagement_impression_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS engagement_clicks ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, impression_id BIGINT UNSIGNED NOT NULL, customer_session_id BIGINT UNSIGNED NOT NULL, action VARCHAR(80) NULL, target_url VARCHAR(500) NULL, clicked_at DATETIME NOT NULL, UNIQUE KEY uq_engagement_click_public (public_id), INDEX idx_engagement_click_hotel (hotel_id,clicked_at), INDEX idx_engagement_click_impression (impression_id,clicked_at), CONSTRAINT fk_engagement_click_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE, CONSTRAINT fk_engagement_click_impression FOREIGN KEY (impression_id) REFERENCES engagement_impressions(id) ON DELETE CASCADE, CONSTRAINT fk_engagement_click_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS engagement_conversions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, hotel_id BIGINT UNSIGNED NOT NULL, impression_id BIGINT UNSIGNED NOT NULL, customer_session_id BIGINT UNSIGNED NOT NULL, conversion_type ENUM('ADD_TO_CART','RESERVATION_CONFIRMED') NOT NULL, resource_public_id VARCHAR(64) NULL, converted_at DATETIME NOT NULL, UNIQUE KEY uq_engagement_conversion_public (public_id), UNIQUE KEY uq_engagement_conversion_once (impression_id,conversion_type,resource_public_id), INDEX idx_engagement_conversion_hotel (hotel_id,converted_at), CONSTRAINT fk_engagement_conversion_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE, CONSTRAINT fk_engagement_conversion_impression FOREIGN KEY (impression_id) REFERENCES engagement_impressions(id) ON DELETE CASCADE, CONSTRAINT fk_engagement_conversion_customer FOREIGN KEY (customer_session_id) REFERENCES customer_sessions(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO permissions (code,name) VALUES ('promotions.view','View promotions, hotel events and engagement analytics'), ('promotions.manage','Manage promotions, hotel events and waiting-time cards') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('SUPER_ADMIN','HOTEL_ADMIN') AND p.code IN ('promotions.view','promotions.manage'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('RESTAURANT_MANAGER','ROOM_SERVICE_MANAGER') AND p.code IN ('promotions.view','promotions.manage'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code='READ_ONLY_MANAGER' AND p.code='promotions.view'; -- ============================================================================ -- MIGRATION: 016_licensing.sql -- ============================================================================ -- Phase 12: commercial licensing, installation binding, feature gates and validation audit. CREATE TABLE IF NOT EXISTS software_licenses ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id CHAR(26) NOT NULL, license_id VARCHAR(100) NOT NULL, customer_name VARCHAR(200) NOT NULL, payload_json LONGTEXT NOT NULL, signature_base64 TEXT NOT NULL, status ENUM('ACTIVE','GRACE','EXPIRED','REVOKED','INVALID') NOT NULL DEFAULT 'ACTIVE', issued_at DATETIME NOT NULL, not_before DATETIME NULL, expires_at DATETIME NOT NULL, grace_until DATETIME NULL, installed_by BIGINT UNSIGNED NULL, installed_at DATETIME NOT NULL, last_validated_at DATETIME NULL, last_validation_code VARCHAR(80) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_software_license_public (public_id), UNIQUE KEY uq_software_license_id (license_id), INDEX idx_software_license_status (status,expires_at), CONSTRAINT fk_software_license_installed_by FOREIGN KEY (installed_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS license_installations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, installation_uuid CHAR(36) NOT NULL, fingerprint_hash CHAR(64) NOT NULL, domain VARCHAR(255) NOT NULL, hostname VARCHAR(255) NOT NULL, database_name VARCHAR(160) NOT NULL, first_seen_at DATETIME NOT NULL, last_seen_at DATETIME NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_license_installation_uuid (installation_uuid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS license_validation_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, software_license_id BIGINT UNSIGNED NULL, installation_id BIGINT UNSIGNED NULL, result_code VARCHAR(80) NOT NULL, result_message VARCHAR(500) NULL, domain VARCHAR(255) NULL, fingerprint_hash CHAR(64) NULL, request_id VARCHAR(80) NULL, ip_address VARCHAR(64) NULL, validated_at DATETIME NOT NULL, INDEX idx_license_validation_time (validated_at), INDEX idx_license_validation_result (result_code,validated_at), CONSTRAINT fk_license_validation_license FOREIGN KEY (software_license_id) REFERENCES software_licenses(id) ON DELETE SET NULL, CONSTRAINT fk_license_validation_installation FOREIGN KEY (installation_id) REFERENCES license_installations(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS license_feature_usage ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, feature_code VARCHAR(80) NOT NULL, usage_date DATE NOT NULL, request_count BIGINT UNSIGNED NOT NULL DEFAULT 0, last_used_at DATETIME NOT NULL, UNIQUE KEY uq_license_feature_usage (feature_code,usage_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO permissions (code,name) VALUES ('license.view','View software license and deployment identity'), ('license.manage','Install and manage software license') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code IN ('SUPER_ADMIN','HOTEL_ADMIN','IT_ADMINISTRATOR') AND p.code IN ('license.view','license.manage'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code='READ_ONLY_MANAGER' AND p.code='license.view'; INSERT IGNORE INTO schema_migrations (migration) VALUES ('001_core.sql'), ('002_auth.sql'), ('003_dining_rooms_qr.sql'), ('004_audit.sql'), ('005_rate_limits.sql'), ('006_menu.sql'), ('007_ordering.sql'), ('008_kitchen_kds.sql'), ('009_room_service.sql'), ('010_reservations.sql'), ('011_billing_payments.sql'), ('012_integrations.sql'), ('013_reporting_analytics.sql'), ('014_runtime_operations.sql'), ('015_guest_engagement.sql'), ('016_licensing.sql'); -- ============================================================================ -- MASTER / DEMO SEED DATA -- ============================================================================ -- Phase 3 idempotent seed for Crowne Plaza Riyadh RDC Hotel & Convention. -- Supported execution path: php bin/seed.php (it also creates the admin password hash and secure QR records). INSERT INTO hotels (public_id,code,name,short_name,city,country,country_code,currency,timezone,default_language,additional_language,status) VALUES ('43505249594144485244433031','CP_RIYADH_RDC','Crowne Plaza Riyadh RDC Hotel & Convention','Crowne Plaza Riyadh RDC','Riyadh','Saudi Arabia','SA','SAR','Asia/Riyadh','en','ar','ACTIVE') ON DUPLICATE KEY UPDATE name=VALUES(name),short_name=VALUES(short_name),city=VALUES(city),country=VALUES(country),currency=VALUES(currency),timezone=VALUES(timezone),default_language=VALUES(default_language),additional_language=VALUES(additional_language),status=VALUES(status); INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'logo_path','/assets/images/hotel-logo.svg','STRING' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value),value_type=VALUES(value_type); INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'wifi_ssid','CrownePlaza_Guest','STRING' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value),value_type=VALUES(value_type); INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'wifi_password','','STRING' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value),value_type=VALUES(value_type); INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'menu_prices_include_tax','1','BOOLEAN' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value),value_type=VALUES(value_type); INSERT INTO tax_rates (hotel_id,code,name,rate,status) SELECT id,'SA_VAT','Saudi VAT',15.0000,'ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),rate=VALUES(rate),status=VALUES(status); INSERT INTO kitchens (hotel_id,code,name,type,status) SELECT id,'MOSAIQUE_KITCHEN','Mosaique Kitchen','RESTAURANT','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO kitchens (hotel_id,code,name,type,status) SELECT id,'KOI_KITCHEN','KOI Kitchen','RESTAURANT','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO kitchens (hotel_id,code,name,type,status) SELECT id,'WILDFIRE_KITCHEN','Wildfire Kitchen','RESTAURANT','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO kitchens (hotel_id,code,name,type,status) SELECT id,'SKYLITE_KITCHEN','SkyLite Kitchen','RESTAURANT','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO kitchens (hotel_id,code,name,type,status) SELECT id,'TEA_GARDEN_KITCHEN','Tea Garden Kitchen','RESTAURANT','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO kitchens (hotel_id,code,name,type,status) SELECT id,'CENTRAL_ROOM_SERVICE_KITCHEN','Central Room Service Kitchen','ROOM_SERVICE','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO kitchen_stations (kitchen_id,code,name,display_order,status) SELECT id,'HOT','Hot Kitchen',10,'ACTIVE' FROM kitchens WHERE code='MOSAIQUE_KITCHEN' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO kitchen_stations (kitchen_id,code,name,display_order,status) SELECT id,'COLD','Cold Kitchen',20,'ACTIVE' FROM kitchens WHERE code='MOSAIQUE_KITCHEN' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO kitchen_stations (kitchen_id,code,name,display_order,status) SELECT id,'GRILL','Grill Station',10,'ACTIVE' FROM kitchens WHERE code='WILDFIRE_KITCHEN' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO kitchen_stations (kitchen_id,code,name,display_order,status) SELECT id,'BEVERAGE','Beverage Station',10,'ACTIVE' FROM kitchens WHERE code='SKYLITE_KITCHEN' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO kitchen_stations (kitchen_id,code,name,display_order,status) SELECT id,'DISPATCH','Dispatch',90,'ACTIVE' FROM kitchens WHERE code='CENTRAL_ROOM_SERVICE_KITCHEN' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurants (hotel_id,public_id,kitchen_id,code,name,type,description,display_order,discoverable,status) SELECT h.id,'4D4F5341495155453030303031',k.id,'MOSAIQUE','Mosaique','RESTAURANT','International dining restaurant.',10,1,'ACTIVE' FROM hotels h JOIN kitchens k ON k.hotel_id=h.id AND k.code='MOSAIQUE_KITCHEN' WHERE h.code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE kitchen_id=VALUES(kitchen_id),name=VALUES(name),type=VALUES(type),description=VALUES(description),display_order=VALUES(display_order),discoverable=VALUES(discoverable),status=VALUES(status); INSERT INTO restaurants (hotel_id,public_id,kitchen_id,code,name,type,description,display_order,discoverable,status) SELECT h.id,'4B4F4930303030303030303031',k.id,'KOI','KOI','RESTAURANT','Japanese–Peruvian / Nikkei dining outlet.',20,1,'ACTIVE' FROM hotels h JOIN kitchens k ON k.hotel_id=h.id AND k.code='KOI_KITCHEN' WHERE h.code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE kitchen_id=VALUES(kitchen_id),name=VALUES(name),description=VALUES(description),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO restaurants (hotel_id,public_id,kitchen_id,code,name,type,description,display_order,discoverable,status) SELECT h.id,'57494C44464952453030303031',k.id,'WILDFIRE','Wildfire','RESTAURANT','Brazilian steakhouse.',30,1,'ACTIVE' FROM hotels h JOIN kitchens k ON k.hotel_id=h.id AND k.code='WILDFIRE_KITCHEN' WHERE h.code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE kitchen_id=VALUES(kitchen_id),name=VALUES(name),description=VALUES(description),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO restaurants (hotel_id,public_id,kitchen_id,code,name,type,description,display_order,discoverable,status) SELECT h.id,'534B594C495445303030303031',k.id,'SKYLITE','SkyLite Lobby Café','CAFE','Lobby café serving beverages, snacks and café items.',40,1,'ACTIVE' FROM hotels h JOIN kitchens k ON k.hotel_id=h.id AND k.code='SKYLITE_KITCHEN' WHERE h.code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE kitchen_id=VALUES(kitchen_id),name=VALUES(name),type=VALUES(type),description=VALUES(description),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO restaurants (hotel_id,public_id,kitchen_id,code,name,type,description,display_order,discoverable,status) SELECT h.id,'54454147415244454E30303031',k.id,'TEA_GARDEN','Tea Garden','CAFE','Tea lounge / café.',50,1,'ACTIVE' FROM hotels h JOIN kitchens k ON k.hotel_id=h.id AND k.code='TEA_GARDEN_KITCHEN' WHERE h.code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE kitchen_id=VALUES(kitchen_id),name=VALUES(name),type=VALUES(type),description=VALUES(description),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO restaurants (hotel_id,public_id,kitchen_id,code,name,type,description,display_order,discoverable,status) SELECT h.id,'524F4F4D534552564943453031',k.id,'IN_ROOM_DINING','In-Room Dining','ROOM_SERVICE','Hotel room-service channel.',60,0,'ACTIVE' FROM hotels h JOIN kitchens k ON k.hotel_id=h.id AND k.code='CENTRAL_ROOM_SERVICE_KITCHEN' WHERE h.code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE kitchen_id=VALUES(kitchen_id),name=VALUES(name),type=VALUES(type),description=VALUES(description),display_order=VALUES(display_order),discoverable=VALUES(discoverable),status=VALUES(status); INSERT INTO restaurant_service_channels (restaurant_id,service_type,enabled) SELECT id,'DINE_IN',1 FROM restaurants WHERE code IN ('MOSAIQUE','KOI','WILDFIRE','SKYLITE','TEA_GARDEN') AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE enabled=1; INSERT INTO restaurant_service_channels (restaurant_id,service_type,enabled) SELECT id,'PRE_ORDER',1 FROM restaurants WHERE code IN ('MOSAIQUE','KOI','WILDFIRE','SKYLITE','TEA_GARDEN') AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE enabled=1; INSERT INTO restaurant_service_channels (restaurant_id,service_type,enabled) SELECT id,'TAKEAWAY',1 FROM restaurants WHERE code IN ('MOSAIQUE','KOI','WILDFIRE','SKYLITE','TEA_GARDEN') AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE enabled=1; INSERT INTO restaurant_service_channels (restaurant_id,service_type,enabled) SELECT id,'ROOM_SERVICE',1 FROM restaurants WHERE code='IN_ROOM_DINING' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE enabled=1; -- Default demo hours are intentionally editable and not represented as official hotel operating hours. INSERT INTO restaurant_hours (restaurant_id,weekday,opens_at,closes_at,is_closed) SELECT r.id,d.day,'06:00:00','23:00:00',0 FROM restaurants r JOIN (SELECT 1 day UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7) d WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND NOT EXISTS (SELECT 1 FROM restaurant_hours h WHERE h.restaurant_id=r.id AND h.weekday=d.day AND h.valid_from IS NULL AND h.valid_to IS NULL); -- Demo physical tables. INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4D4F534D303100000000000000','M01','Table M01',4,1,4,'AVAILABLE' FROM restaurants WHERE code='MOSAIQUE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4D4F534D303200000000000000','M02','Table M02',4,1,4,'AVAILABLE' FROM restaurants WHERE code='MOSAIQUE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4D4F534D303300000000000000','M03','Table M03',4,1,4,'AVAILABLE' FROM restaurants WHERE code='MOSAIQUE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4D4F534D303400000000000000','M04','Table M04',4,1,4,'AVAILABLE' FROM restaurants WHERE code='MOSAIQUE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4D4F534D303500000000000000','M05','Table M05',4,1,4,'AVAILABLE' FROM restaurants WHERE code='MOSAIQUE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4D4F534D303600000000000000','M06','Table M06',4,1,4,'AVAILABLE' FROM restaurants WHERE code='MOSAIQUE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4D4F534D303700000000000000','M07','Table M07',4,1,4,'AVAILABLE' FROM restaurants WHERE code='MOSAIQUE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4D4F534D303800000000000000','M08','Table M08',4,1,4,'AVAILABLE' FROM restaurants WHERE code='MOSAIQUE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4D4F534D303900000000000000','M09','Table M09',4,1,4,'AVAILABLE' FROM restaurants WHERE code='MOSAIQUE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4D4F534D313000000000000000','M10','Table M10',4,1,4,'AVAILABLE' FROM restaurants WHERE code='MOSAIQUE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4B4F494B303100000000000000','K01','Table K01',4,1,4,'AVAILABLE' FROM restaurants WHERE code='KOI' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4B4F494B303200000000000000','K02','Table K02',4,1,4,'AVAILABLE' FROM restaurants WHERE code='KOI' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4B4F494B303300000000000000','K03','Table K03',4,1,4,'AVAILABLE' FROM restaurants WHERE code='KOI' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4B4F494B303400000000000000','K04','Table K04',4,1,4,'AVAILABLE' FROM restaurants WHERE code='KOI' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4B4F494B303500000000000000','K05','Table K05',4,1,4,'AVAILABLE' FROM restaurants WHERE code='KOI' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4B4F494B303600000000000000','K06','Table K06',4,1,4,'AVAILABLE' FROM restaurants WHERE code='KOI' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4B4F494B303700000000000000','K07','Table K07',4,1,4,'AVAILABLE' FROM restaurants WHERE code='KOI' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'4B4F494B303800000000000000','K08','Table K08',4,1,4,'AVAILABLE' FROM restaurants WHERE code='KOI' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'57494C57303100000000000000','W01','Table W01',4,1,4,'AVAILABLE' FROM restaurants WHERE code='WILDFIRE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'57494C57303200000000000000','W02','Table W02',4,1,4,'AVAILABLE' FROM restaurants WHERE code='WILDFIRE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'57494C57303300000000000000','W03','Table W03',4,1,4,'AVAILABLE' FROM restaurants WHERE code='WILDFIRE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'57494C57303400000000000000','W04','Table W04',4,1,4,'AVAILABLE' FROM restaurants WHERE code='WILDFIRE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'57494C57303500000000000000','W05','Table W05',4,1,4,'AVAILABLE' FROM restaurants WHERE code='WILDFIRE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'57494C57303600000000000000','W06','Table W06',4,1,4,'AVAILABLE' FROM restaurants WHERE code='WILDFIRE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'57494C57303700000000000000','W07','Table W07',4,1,4,'AVAILABLE' FROM restaurants WHERE code='WILDFIRE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'57494C57303800000000000000','W08','Table W08',4,1,4,'AVAILABLE' FROM restaurants WHERE code='WILDFIRE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'534B5953303100000000000000','S01','Table S01',4,1,4,'AVAILABLE' FROM restaurants WHERE code='SKYLITE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'534B5953303200000000000000','S02','Table S02',4,1,4,'AVAILABLE' FROM restaurants WHERE code='SKYLITE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'534B5953303300000000000000','S03','Table S03',4,1,4,'AVAILABLE' FROM restaurants WHERE code='SKYLITE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'534B5953303400000000000000','S04','Table S04',4,1,4,'AVAILABLE' FROM restaurants WHERE code='SKYLITE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'534B5953303500000000000000','S05','Table S05',4,1,4,'AVAILABLE' FROM restaurants WHERE code='SKYLITE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'534B5953303600000000000000','S06','Table S06',4,1,4,'AVAILABLE' FROM restaurants WHERE code='SKYLITE' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'54454154303100000000000000','T01','Table T01',4,1,4,'AVAILABLE' FROM restaurants WHERE code='TEA_GARDEN' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'54454154303200000000000000','T02','Table T02',4,1,4,'AVAILABLE' FROM restaurants WHERE code='TEA_GARDEN' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'54454154303300000000000000','T03','Table T03',4,1,4,'AVAILABLE' FROM restaurants WHERE code='TEA_GARDEN' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'54454154303400000000000000','T04','Table T04',4,1,4,'AVAILABLE' FROM restaurants WHERE code='TEA_GARDEN' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'54454154303500000000000000','T05','Table T05',4,1,4,'AVAILABLE' FROM restaurants WHERE code='TEA_GARDEN' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO restaurant_tables (restaurant_id,public_id,code,name,capacity,minimum_capacity,maximum_capacity,status) SELECT id,'54454154303600000000000000','T06','Table T06',4,1,4,'AVAILABLE' FROM restaurants WHERE code='TEA_GARDEN' AND hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); -- Demo rooms. INSERT INTO hotel_rooms (hotel_id,public_id,room_number,floor,status) SELECT id,'524F4F4D313230310000000000','1201','12','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE floor=VALUES(floor),status=VALUES(status); INSERT INTO hotel_rooms (hotel_id,public_id,room_number,floor,status) SELECT id,'524F4F4D313230320000000000','1202','12','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE floor=VALUES(floor),status=VALUES(status); INSERT INTO hotel_rooms (hotel_id,public_id,room_number,floor,status) SELECT id,'524F4F4D313230330000000000','1203','12','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE floor=VALUES(floor),status=VALUES(status); INSERT INTO hotel_rooms (hotel_id,public_id,room_number,floor,status) SELECT id,'524F4F4D313230340000000000','1204','12','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE floor=VALUES(floor),status=VALUES(status); INSERT INTO hotel_rooms (hotel_id,public_id,room_number,floor,status) SELECT id,'524F4F4D313230350000000000','1205','12','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE floor=VALUES(floor),status=VALUES(status); INSERT INTO hotel_rooms (hotel_id,public_id,room_number,floor,status) SELECT id,'524F4F4D313230360000000000','1206','12','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE floor=VALUES(floor),status=VALUES(status); INSERT INTO hotel_rooms (hotel_id,public_id,room_number,floor,status) SELECT id,'524F4F4D313230370000000000','1207','12','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE floor=VALUES(floor),status=VALUES(status); INSERT INTO hotel_rooms (hotel_id,public_id,room_number,floor,status) SELECT id,'524F4F4D313230380000000000','1208','12','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE floor=VALUES(floor),status=VALUES(status); INSERT INTO hotel_rooms (hotel_id,public_id,room_number,floor,status) SELECT id,'524F4F4D313230390000000000','1209','12','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE floor=VALUES(floor),status=VALUES(status); INSERT INTO hotel_rooms (hotel_id,public_id,room_number,floor,status) SELECT id,'524F4F4D313231300000000000','1210','12','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE floor=VALUES(floor),status=VALUES(status); -- Database-driven permissions and roles. INSERT INTO permissions (code,name) VALUES ('*','All permissions'), ('hotel.manage','Manage hotel settings'), ('restaurants.view','View restaurants'), ('restaurants.manage','Manage restaurants'), ('rooms.view','View rooms'), ('rooms.manage','Manage rooms'), ('tables.view','View tables'), ('tables.manage','Manage tables'), ('qr.manage','Manage and print QR codes'), ('kitchen.view','View kitchen information'), ('reports.view','View reports') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT INTO roles (hotel_id,code,name) SELECT id,'SUPER_ADMIN','Super Admin' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT INTO roles (hotel_id,code,name) SELECT id,'HOTEL_ADMIN','Hotel Admin' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT INTO roles (hotel_id,code,name) SELECT id,'RESTAURANT_MANAGER','Restaurant Manager' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT INTO roles (hotel_id,code,name) SELECT id,'KITCHEN_MANAGER','Kitchen Manager' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT INTO roles (hotel_id,code,name) SELECT id,'KITCHEN_STAFF','Kitchen Staff' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT INTO roles (hotel_id,code,name) SELECT id,'ROOM_SERVICE_MANAGER','Room Service Manager' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT INTO roles (hotel_id,code,name) SELECT id,'ROOM_SERVICE_STAFF','Room Service Staff' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT INTO roles (hotel_id,code,name) SELECT id,'WAITER','Waiter' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT INTO roles (hotel_id,code,name) SELECT id,'CASHIER','Cashier' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT INTO roles (hotel_id,code,name) SELECT id,'BILLING_STAFF','Billing Staff' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT INTO roles (hotel_id,code,name) SELECT id,'IT_ADMINISTRATOR','IT Administrator' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT INTO roles (hotel_id,code,name) SELECT id,'READ_ONLY_MANAGER','Read Only Manager' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('SUPER_ADMIN','HOTEL_ADMIN'); -- The supported PHP seed runner immediately replaces this disabled placeholder with a password_hash() value from .env. INSERT INTO users (hotel_id,email,username,password_hash,name,status) SELECT id,'admin@example.com','admin','__GENERATED_BY_SEED_RUNNER__','Hotel Administrator','DISABLED' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE hotel_id=VALUES(hotel_id),name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.code IN ('restaurants.view','restaurants.manage','tables.view','tables.manage','qr.manage') WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code='RESTAURANT_MANAGER'; INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.code IN ('restaurants.view','tables.view','kitchen.view') WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('KITCHEN_MANAGER','KITCHEN_STAFF'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.code IN ('rooms.view','restaurants.view') WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('ROOM_SERVICE_MANAGER','ROOM_SERVICE_STAFF'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.code IN ('restaurants.view','tables.view') WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code='WAITER'; INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.code IN ('hotel.manage','restaurants.view','restaurants.manage','rooms.view','rooms.manage','tables.view','tables.manage','qr.manage') WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code='IT_ADMINISTRATOR'; INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.code IN ('restaurants.view','rooms.view','tables.view','reports.view') WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code='READ_ONLY_MANAGER'; -- ===== PHASE 2 MENU SEED ===== -- Demo categories/items only. They are intentionally editable and are not represented as the hotel's official complete menu. INSERT INTO permissions (code,name) VALUES ('menu.view','View menu configuration'),('menu.manage','Manage menu configuration') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('SUPER_ADMIN','HOTEL_ADMIN') AND p.code IN ('menu.view','menu.manage'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code='RESTAURANT_MANAGER' AND p.code IN ('menu.view','menu.manage'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('KITCHEN_MANAGER','KITCHEN_STAFF','ROOM_SERVICE_MANAGER','ROOM_SERVICE_STAFF','READ_ONLY_MANAGER') AND p.code='menu.view'; INSERT INTO ingredients (hotel_id,code,name,status) SELECT id,'CHICKEN','Chicken','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO ingredients (hotel_id,code,name,status) SELECT id,'BEEF','Beef','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO ingredients (hotel_id,code,name,status) SELECT id,'SALMON','Salmon','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO ingredients (hotel_id,code,name,status) SELECT id,'LETTUCE','Lettuce','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO ingredients (hotel_id,code,name,status) SELECT id,'TOMATO','Tomato','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO ingredients (hotel_id,code,name,status) SELECT id,'MILK','Milk','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO ingredients (hotel_id,code,name,status) SELECT id,'COFFEE','Coffee','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO ingredients (hotel_id,code,name,status) SELECT id,'TEA','Tea','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO ingredients (hotel_id,code,name,status) SELECT id,'WHEAT','Wheat','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO ingredients (hotel_id,code,name,status) SELECT id,'EGG','Egg','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO ingredients (hotel_id,code,name,status) SELECT id,'CHOCOLATE','Chocolate','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO ingredients (hotel_id,code,name,status) SELECT id,'ORANGE','Orange','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),status=VALUES(status); INSERT INTO allergens (hotel_id,code,name,description,status) SELECT id,'GLUTEN','Gluten','Contains gluten cereals.','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),description=VALUES(description),status=VALUES(status); INSERT INTO allergens (hotel_id,code,name,description,status) SELECT id,'DAIRY','Dairy','Contains milk or dairy.','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),description=VALUES(description),status=VALUES(status); INSERT INTO allergens (hotel_id,code,name,description,status) SELECT id,'EGG','Egg','Contains egg.','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),description=VALUES(description),status=VALUES(status); INSERT INTO allergens (hotel_id,code,name,description,status) SELECT id,'FISH','Fish','Contains fish.','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),description=VALUES(description),status=VALUES(status); INSERT INTO allergens (hotel_id,code,name,description,status) SELECT id,'SOY','Soy','Contains soy.','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),description=VALUES(description),status=VALUES(status); INSERT INTO allergens (hotel_id,code,name,description,status) SELECT id,'SESAME','Sesame','Contains sesame.','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),description=VALUES(description),status=VALUES(status); INSERT INTO allergens (hotel_id,code,name,description,status) SELECT id,'NUTS','Tree Nuts','May contain tree nuts.','ACTIVE' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),description=VALUES(description),status=VALUES(status); INSERT INTO addons (public_id,hotel_id,tax_rate_id,code,name,price,status) SELECT '5BB5B06E2B468C1899B1BCCA8C',h.id,t.id,'EXTRA_CHEESE','Extra Cheese',8.00,'ACTIVE' FROM hotels h JOIN tax_rates t ON t.hotel_id=h.id AND t.code='SA_VAT' WHERE h.code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),price=VALUES(price),tax_rate_id=VALUES(tax_rate_id),status=VALUES(status); INSERT INTO addons (public_id,hotel_id,tax_rate_id,code,name,price,status) SELECT 'C837C2416946F217CD6FA39C0D',h.id,t.id,'EXTRA_SAUCE','Extra Sauce',5.00,'ACTIVE' FROM hotels h JOIN tax_rates t ON t.hotel_id=h.id AND t.code='SA_VAT' WHERE h.code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),price=VALUES(price),tax_rate_id=VALUES(tax_rate_id),status=VALUES(status); INSERT INTO addons (public_id,hotel_id,tax_rate_id,code,name,price,status) SELECT '6CC412BE391440C114ACF87254',h.id,t.id,'AVOCADO','Avocado',10.00,'ACTIVE' FROM hotels h JOIN tax_rates t ON t.hotel_id=h.id AND t.code='SA_VAT' WHERE h.code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE name=VALUES(name),price=VALUES(price),tax_rate_id=VALUES(tax_rate_id),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '11579D3C45124E199999CAE0B3',r.id,'BREAKFAST','Breakfast',10,'ACTIVE' FROM restaurants r WHERE r.code='MOSAIQUE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '7E3868629F3D6A5C6BD04BAEA0',r.id,'STARTERS','Starters',20,'ACTIVE' FROM restaurants r WHERE r.code='MOSAIQUE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '7414AB90EC2052A4B8DE320D9E',r.id,'SOUPS','Soups',30,'ACTIVE' FROM restaurants r WHERE r.code='MOSAIQUE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '06DEF87C871A6A0AACE19D9D46',r.id,'SALADS','Salads',40,'ACTIVE' FROM restaurants r WHERE r.code='MOSAIQUE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT 'B56CDED60B3BBF7D4F2332F846',r.id,'MAIN_COURSE','Main Course',50,'ACTIVE' FROM restaurants r WHERE r.code='MOSAIQUE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '5D1A949143BA0177A7022EBF29',r.id,'DESSERTS','Desserts',60,'ACTIVE' FROM restaurants r WHERE r.code='MOSAIQUE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '1AACAC6E79721CE5D378D8ABCB',r.id,'BEVERAGES','Beverages',70,'ACTIVE' FROM restaurants r WHERE r.code='MOSAIQUE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT 'C1BF48F167BA6C1D0825CB44A8',r.id,'COFFEE','Coffee',80,'ACTIVE' FROM restaurants r WHERE r.code='MOSAIQUE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '1581DC211261D928C9404B7523',r.id,'TEA','Tea',90,'ACTIVE' FROM restaurants r WHERE r.code='MOSAIQUE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '2ACD9EF0D98EAB2B0B496D7FD2',r.id,'SUSHI','Sushi',10,'ACTIVE' FROM restaurants r WHERE r.code='KOI' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '13EB48FBA3A9867E448F104DFD',r.id,'SASHIMI','Sashimi',20,'ACTIVE' FROM restaurants r WHERE r.code='KOI' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT 'E3B61351B8A6857EECBE1F4955',r.id,'JAPANESE','Japanese',30,'ACTIVE' FROM restaurants r WHERE r.code='KOI' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '18221DDBA90C9BC623E40AE425',r.id,'PERUVIAN','Peruvian',40,'ACTIVE' FROM restaurants r WHERE r.code='KOI' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '1EA9902C23F0B70A6512CBECEA',r.id,'NIKKEI','Nikkei',50,'ACTIVE' FROM restaurants r WHERE r.code='KOI' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '042D1CDEF6CF783D9F51EAAAE8',r.id,'STARTERS','Starters',60,'ACTIVE' FROM restaurants r WHERE r.code='KOI' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '5E67F43297F9AC5CA024DE872B',r.id,'MAIN_COURSE','Main Course',70,'ACTIVE' FROM restaurants r WHERE r.code='KOI' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT 'A710F2A547EAD191F15144AEAE',r.id,'DESSERTS','Desserts',80,'ACTIVE' FROM restaurants r WHERE r.code='KOI' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '6607CB3082C4F46C3789DB3B60',r.id,'BEVERAGES','Beverages',90,'ACTIVE' FROM restaurants r WHERE r.code='KOI' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '1E30648C08773E13E4D74C920B',r.id,'STARTERS','Starters',10,'ACTIVE' FROM restaurants r WHERE r.code='WILDFIRE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '01718041F4F4F2143337490017',r.id,'SALADS','Salads',20,'ACTIVE' FROM restaurants r WHERE r.code='WILDFIRE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '80F1FA0DC0DB9F8E6293E52AB8',r.id,'SOUPS','Soups',30,'ACTIVE' FROM restaurants r WHERE r.code='WILDFIRE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '5B3F4EF59A1E59DAB86E44D51C',r.id,'BRAZILIAN_GRILL','Brazilian Grill',40,'ACTIVE' FROM restaurants r WHERE r.code='WILDFIRE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT 'A4873D591853BF5BD5D0C117EC',r.id,'STEAK','Steak',50,'ACTIVE' FROM restaurants r WHERE r.code='WILDFIRE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '0349D554CD0FA7ED75312C3C1D',r.id,'CHICKEN','Chicken',60,'ACTIVE' FROM restaurants r WHERE r.code='WILDFIRE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '23DE8AFB76C0A46D805B2661A0',r.id,'SEAFOOD','Seafood',70,'ACTIVE' FROM restaurants r WHERE r.code='WILDFIRE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '4762F400FDF2C80F5176BA4F32',r.id,'SIDES','Sides',80,'ACTIVE' FROM restaurants r WHERE r.code='WILDFIRE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '90DCCED53BDC09601A973FF26C',r.id,'DESSERTS','Desserts',90,'ACTIVE' FROM restaurants r WHERE r.code='WILDFIRE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '31CE432DE989B60CA0AC1C67E4',r.id,'BEVERAGES','Beverages',100,'ACTIVE' FROM restaurants r WHERE r.code='WILDFIRE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT 'FC5C7E0AAC6AE07981BC209114',r.id,'COFFEE','Coffee',10,'ACTIVE' FROM restaurants r WHERE r.code='SKYLITE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT 'DC02A7E8AD4951F4D089436355',r.id,'TEA','Tea',20,'ACTIVE' FROM restaurants r WHERE r.code='SKYLITE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '8891430EE6054F030151FA34D3',r.id,'COLD_BEVERAGES','Cold Beverages',30,'ACTIVE' FROM restaurants r WHERE r.code='SKYLITE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT 'CAABE61AB370F43228DC923416',r.id,'FRESH_JUICE','Fresh Juice',40,'ACTIVE' FROM restaurants r WHERE r.code='SKYLITE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '6217DE02896783DB5110E2E33C',r.id,'PASTRIES','Pastries',50,'ACTIVE' FROM restaurants r WHERE r.code='SKYLITE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT 'C1097A333F9DE42B1B4E269969',r.id,'CAKES','Cakes',60,'ACTIVE' FROM restaurants r WHERE r.code='SKYLITE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT 'D2A02EFD286C25388F13D75641',r.id,'SANDWICHES','Sandwiches',70,'ACTIVE' FROM restaurants r WHERE r.code='SKYLITE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '692C8FED8C1F7E22A76F87F719',r.id,'SNACKS','Snacks',80,'ACTIVE' FROM restaurants r WHERE r.code='SKYLITE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '499604C15C9A5198E4CA91E4FA',r.id,'DESSERTS','Desserts',90,'ACTIVE' FROM restaurants r WHERE r.code='SKYLITE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '1F3C5B880C9DF929DB7F22B19F',r.id,'TEA','Tea',10,'ACTIVE' FROM restaurants r WHERE r.code='TEA_GARDEN' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT 'F8FB54A1E3F157BB437EFAD35A',r.id,'COFFEE','Coffee',20,'ACTIVE' FROM restaurants r WHERE r.code='TEA_GARDEN' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '1CDFE11768BBE28A12EA44C2F3',r.id,'AFTERNOON_TEA','Afternoon Tea',30,'ACTIVE' FROM restaurants r WHERE r.code='TEA_GARDEN' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '1FBB45C55DCCCCF0613AC4D7B9',r.id,'PASTRIES','Pastries',40,'ACTIVE' FROM restaurants r WHERE r.code='TEA_GARDEN' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '0EB47073463E330BA567C5F990',r.id,'CAKES','Cakes',50,'ACTIVE' FROM restaurants r WHERE r.code='TEA_GARDEN' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT 'A874E974F6D6C474C914CCD700',r.id,'SANDWICHES','Sandwiches',60,'ACTIVE' FROM restaurants r WHERE r.code='TEA_GARDEN' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT 'E8BACE16859E2E6D4C178F8696',r.id,'LIGHT_MEALS','Light Meals',70,'ACTIVE' FROM restaurants r WHERE r.code='TEA_GARDEN' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT 'DC6DBEAE1AB7A588F03ECFF9BA',r.id,'DESSERTS','Desserts',80,'ACTIVE' FROM restaurants r WHERE r.code='TEA_GARDEN' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '8F81CB076093BBA370B0A1ED65',r.id,'BREAKFAST','Breakfast',10,'ACTIVE' FROM restaurants r WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '0279FB74E5699E405C09309342',r.id,'ALL_DAY_DINING','All Day Dining',20,'ACTIVE' FROM restaurants r WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '89B18AAC59AC085DCEF9932944',r.id,'STARTERS','Starters',30,'ACTIVE' FROM restaurants r WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT 'FB02CE063A5899D93502C24DB5',r.id,'MAIN_COURSE','Main Course',40,'ACTIVE' FROM restaurants r WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '5D74E3DFF21D65287840A1C2DA',r.id,'SANDWICHES','Sandwiches',50,'ACTIVE' FROM restaurants r WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '033F38984E357378AD9FCE0E52',r.id,'DESSERTS','Desserts',60,'ACTIVE' FROM restaurants r WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '41E66C538AC728C02EB17560E7',r.id,'BEVERAGES','Beverages',70,'ACTIVE' FROM restaurants r WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT 'BACB663090A4E6FAD0704BA6BB',r.id,'COFFEE','Coffee',80,'ACTIVE' FROM restaurants r WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '61D4BCA1E44BD02A676E7C683D',r.id,'TEA','Tea',90,'ACTIVE' FROM restaurants r WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO categories (public_id,restaurant_id,code,name,display_order,status) SELECT '60EC575BB4D8A26A707B950C69',r.id,'KIDS_MENU','Kids Menu',100,'ACTIVE' FROM restaurants r WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE name=VALUES(name),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '689EAA0ED2413B052254CFC9CF',r.id,c.id,t.id,'Continental Breakfast','Selection of breakfast favourites for development/testing.',85.00,620,15,15,10,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='BREAKFAST' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='MOSAIQUE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '6CB0BF43FF51EA5030DD5DBED7',r.id,c.id,t.id,'Chicken Caesar Salad','Classic Caesar-style salad with grilled chicken.',68.00,510,12,12,20,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='SALADS' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='MOSAIQUE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '3EE460F5FDA19A63A196735557',r.id,c.id,t.id,'Grilled Chicken','Grilled chicken main course.',95.00,560,20,20,30,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='MAIN_COURSE' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='MOSAIQUE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '9E98233AE705703F64B9C3FFB0',r.id,c.id,t.id,'Chocolate Cake','Chocolate cake slice.',42.00,430,5,5,40,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='DESSERTS' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='MOSAIQUE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '6A498D545586674F27E1D3C3DA',r.id,c.id,t.id,'Fresh Orange Juice','Fresh orange juice.',28.00,120,4,4,50,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='BEVERAGES' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='MOSAIQUE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT 'FA7BFE0FF891E25B7C43ABC0C3',r.id,c.id,t.id,'Arabic Coffee','Traditional Arabic coffee.',24.00,30,5,5,60,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='COFFEE' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='MOSAIQUE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '45B7E59983A29AA06B9E119794',r.id,c.id,t.id,'Salmon Sushi','Demo salmon sushi selection.',72.00,340,15,15,10,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='SUSHI' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='KOI' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '321506FC7C27EDA07052C48C53',r.id,c.id,t.id,'Chicken Yakitori','Grilled chicken skewers, demo item.',78.00,420,18,18,20,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='JAPANESE' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='KOI' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT 'CC7E18AD023F21FD8CB9C0DC3D',r.id,c.id,t.id,'Beef Teriyaki','Beef teriyaki, demo item.',110.00,650,22,22,30,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='MAIN_COURSE' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='KOI' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '01FDE980DEA8FBDAA0A8ACF0F7',r.id,c.id,t.id,'Miso Soup','Miso soup.',36.00,120,8,8,40,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='STARTERS' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='KOI' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '82455684194C06346ACE104379',r.id,c.id,t.id,'Mango Mochi','Mango mochi dessert.',44.00,290,5,5,50,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='DESSERTS' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='KOI' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '646A37A5C55594984C64C42B92',r.id,c.id,t.id,'Green Tea','Green tea.',22.00,5,4,4,60,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='BEVERAGES' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='KOI' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '31935987ECEE3353A8D9F38379',r.id,c.id,t.id,'Grilled Beef','Brazilian-style grilled beef, demo item.',145.00,780,25,25,10,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='BRAZILIAN_GRILL' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='WILDFIRE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '4A8F32CEA6D0A1D8179D242FA0',r.id,c.id,t.id,'Chicken Skewer','Grilled chicken skewer.',82.00,460,18,18,20,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='CHICKEN' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='WILDFIRE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '012D3DE45D333FF3A8DED02236',r.id,c.id,t.id,'Brazilian Sausage','Brazilian-style sausage, demo item.',74.00,520,15,15,30,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='BRAZILIAN_GRILL' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='WILDFIRE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '3974AC485CA0C79DA1626A336A',r.id,c.id,t.id,'Grilled Vegetables','Seasonal grilled vegetables.',38.00,210,12,12,40,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='SIDES' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='WILDFIRE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '13833EC4C8D7F701E1FE65956C',r.id,c.id,t.id,'Chocolate Dessert','Chocolate dessert.',48.00,440,6,6,50,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='DESSERTS' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='WILDFIRE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT 'CCF3F2565C51E7E1CB6951E7AA',r.id,c.id,t.id,'Fresh Juice','Fresh juice.',30.00,130,4,4,60,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='BEVERAGES' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='WILDFIRE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT 'C029A15B7E66C71558A8841DC1',r.id,c.id,t.id,'Cappuccino','Espresso with steamed milk.',26.00,110,5,5,10,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='COFFEE' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='SKYLITE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '9AFBEFCB99FDFAF960C57C01D4',r.id,c.id,t.id,'Latte','Espresso with steamed milk.',28.00,150,5,5,20,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='COFFEE' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='SKYLITE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '6507527DC0D61E00F214FB064D',r.id,c.id,t.id,'Americano','Espresso with hot water.',22.00,15,4,4,30,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='COFFEE' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='SKYLITE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '324E641863638FAA1D12F1AAD6',r.id,c.id,t.id,'Fresh Orange Juice','Fresh orange juice.',28.00,120,4,4,40,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='FRESH_JUICE' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='SKYLITE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT 'ECD606E3A340F1DC2D954CC6E6',r.id,c.id,t.id,'Cheesecake','Cheesecake slice.',38.00,390,5,5,50,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='CAKES' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='SKYLITE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '7D01FAEF55670E2E3EF113E8A9',r.id,c.id,t.id,'Club Sandwich','Classic club sandwich.',58.00,650,15,15,60,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='SANDWICHES' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='SKYLITE' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT 'D3C5520C04E17E3D9A07C72086',r.id,c.id,t.id,'Arabic Tea','Arabic tea.',22.00,10,4,4,10,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='TEA' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='TEA_GARDEN' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT 'C27221278EFF84C3575C5DFD86',r.id,c.id,t.id,'English Tea','English tea.',22.00,10,4,4,20,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='TEA' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='TEA_GARDEN' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '784E544537E3CD783C21B0D300',r.id,c.id,t.id,'Afternoon Tea','Demo afternoon tea set.',165.00,900,20,20,30,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='AFTERNOON_TEA' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='TEA_GARDEN' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT 'E408A0D5CA63F8163760AB6F46',r.id,c.id,t.id,'Chocolate Cake','Chocolate cake slice.',42.00,430,5,5,40,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='CAKES' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='TEA_GARDEN' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '596747059E9B307CFFF1BC4A2A',r.id,c.id,t.id,'Croissant','Butter croissant.',24.00,280,5,5,50,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='PASTRIES' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='TEA_GARDEN' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '89F947E0DC39B1674A0B86C907',r.id,c.id,t.id,'Sandwich','Light sandwich, demo item.',48.00,460,12,12,60,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='SANDWICHES' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='TEA_GARDEN' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '34E323769C8DCF16FFFBD742EF',r.id,c.id,t.id,'Club Sandwich','Room-service club sandwich.',65.00,680,18,18,10,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='SANDWICHES' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT 'FB1019D0E4B8855BB45B73FDFF',r.id,c.id,t.id,'Chicken Burger','Chicken burger with accompaniments.',72.00,740,20,20,20,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='MAIN_COURSE' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT 'FBF24EF37B182D9016623F64F1',r.id,c.id,t.id,'Caesar Salad','Caesar salad.',56.00,380,12,12,30,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='STARTERS' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '989993153216A3C5B194A74771',r.id,c.id,t.id,'Margherita Pizza','Margherita pizza.',68.00,760,22,22,40,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='MAIN_COURSE' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT '3BE74F365440DA72676C1FEF27',r.id,c.id,t.id,'Fruit Plate','Fresh fruit plate.',46.00,220,8,8,50,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='DESSERTS' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT 'A6998DC32DE56416B75FE6A159',r.id,c.id,t.id,'Coffee','Freshly brewed coffee.',24.00,20,5,5,60,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='COFFEE' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT 'DEE47E1E787728FFAC08F2D812',r.id,c.id,t.id,'Tea','Tea selection.',22.00,10,5,5,70,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='TEA' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_items (public_id,restaurant_id,category_id,tax_rate_id,name,description,price,calories,preparation_time,serving_time,display_order,featured,status) SELECT 'F9BA28FD75B23C11AEEFABA2DC',r.id,c.id,t.id,'Fresh Juice','Fresh juice.',30.00,130,5,5,80,0,'ACTIVE' FROM restaurants r JOIN categories c ON c.restaurant_id=r.id AND c.code='BEVERAGES' JOIN tax_rates t ON t.hotel_id=r.hotel_id AND t.code='SA_VAT' WHERE r.code='IN_ROOM_DINING' AND r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') ON DUPLICATE KEY UPDATE category_id=VALUES(category_id),tax_rate_id=VALUES(tax_rate_id),name=VALUES(name),description=VALUES(description),price=VALUES(price),calories=VALUES(calories),preparation_time=VALUES(preparation_time),serving_time=VALUES(serving_time),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_item_variants (menu_item_id,code,name,price_delta,display_order,status) SELECT id,'SMALL','Small',0.00,10,'ACTIVE' FROM menu_items WHERE public_id='C029A15B7E66C71558A8841DC1' ON DUPLICATE KEY UPDATE name=VALUES(name),price_delta=VALUES(price_delta),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_item_variants (menu_item_id,code,name,price_delta,display_order,status) SELECT id,'MEDIUM','Medium',4.00,20,'ACTIVE' FROM menu_items WHERE public_id='C029A15B7E66C71558A8841DC1' ON DUPLICATE KEY UPDATE name=VALUES(name),price_delta=VALUES(price_delta),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_item_variants (menu_item_id,code,name,price_delta,display_order,status) SELECT id,'LARGE','Large',8.00,30,'ACTIVE' FROM menu_items WHERE public_id='C029A15B7E66C71558A8841DC1' ON DUPLICATE KEY UPDATE name=VALUES(name),price_delta=VALUES(price_delta),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_item_variants (menu_item_id,code,name,price_delta,display_order,status) SELECT id,'SMALL','Small',0.00,10,'ACTIVE' FROM menu_items WHERE public_id='A6998DC32DE56416B75FE6A159' ON DUPLICATE KEY UPDATE name=VALUES(name),price_delta=VALUES(price_delta),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_item_variants (menu_item_id,code,name,price_delta,display_order,status) SELECT id,'MEDIUM','Medium',4.00,20,'ACTIVE' FROM menu_items WHERE public_id='A6998DC32DE56416B75FE6A159' ON DUPLICATE KEY UPDATE name=VALUES(name),price_delta=VALUES(price_delta),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_item_variants (menu_item_id,code,name,price_delta,display_order,status) SELECT id,'LARGE','Large',8.00,30,'ACTIVE' FROM menu_items WHERE public_id='A6998DC32DE56416B75FE6A159' ON DUPLICATE KEY UPDATE name=VALUES(name),price_delta=VALUES(price_delta),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_item_variants (menu_item_id,code,name,price_delta,display_order,status) SELECT id,'SMALL','Small',0.00,10,'ACTIVE' FROM menu_items WHERE public_id='6A498D545586674F27E1D3C3DA' ON DUPLICATE KEY UPDATE name=VALUES(name),price_delta=VALUES(price_delta),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_item_variants (menu_item_id,code,name,price_delta,display_order,status) SELECT id,'LARGE','Large',8.00,20,'ACTIVE' FROM menu_items WHERE public_id='6A498D545586674F27E1D3C3DA' ON DUPLICATE KEY UPDATE name=VALUES(name),price_delta=VALUES(price_delta),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_item_flavours (menu_item_id,code,name,price_delta,display_order,status) SELECT id,'VANILLA','Vanilla',3.00,10,'ACTIVE' FROM menu_items WHERE public_id='9AFBEFCB99FDFAF960C57C01D4' ON DUPLICATE KEY UPDATE name=VALUES(name),price_delta=VALUES(price_delta),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_item_flavours (menu_item_id,code,name,price_delta,display_order,status) SELECT id,'CARAMEL','Caramel',3.00,20,'ACTIVE' FROM menu_items WHERE public_id='9AFBEFCB99FDFAF960C57C01D4' ON DUPLICATE KEY UPDATE name=VALUES(name),price_delta=VALUES(price_delta),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_item_flavours (menu_item_id,code,name,price_delta,display_order,status) SELECT id,'VANILLA','Vanilla',3.00,10,'ACTIVE' FROM menu_items WHERE public_id='A6998DC32DE56416B75FE6A159' ON DUPLICATE KEY UPDATE name=VALUES(name),price_delta=VALUES(price_delta),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO menu_item_flavours (menu_item_id,code,name,price_delta,display_order,status) SELECT id,'CHOCOLATE','Chocolate',3.00,20,'ACTIVE' FROM menu_items WHERE public_id='A6998DC32DE56416B75FE6A159' ON DUPLICATE KEY UPDATE name=VALUES(name),price_delta=VALUES(price_delta),display_order=VALUES(display_order),status=VALUES(status); INSERT INTO availability_rules (menu_item_id,name,weekday_mask,month_mask,time_from,time_to,display_order,status) SELECT mi.id,'Breakfast Hours',0,0,'06:00:00','11:00:00',10,'ACTIVE' FROM menu_items mi WHERE mi.public_id='689EAA0ED2413B052254CFC9CF' AND NOT EXISTS (SELECT 1 FROM availability_rules ar WHERE ar.menu_item_id=mi.id AND ar.name='Breakfast Hours'); INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='CHICKEN' WHERE mi.public_id='6CB0BF43FF51EA5030DD5DBED7'; INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='LETTUCE' WHERE mi.public_id='6CB0BF43FF51EA5030DD5DBED7'; INSERT IGNORE INTO menu_item_allergens (menu_item_id,allergen_id) SELECT mi.id,a.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN allergens a ON a.hotel_id=r.hotel_id AND a.code='DAIRY' WHERE mi.public_id='6CB0BF43FF51EA5030DD5DBED7'; INSERT IGNORE INTO menu_item_allergens (menu_item_id,allergen_id) SELECT mi.id,a.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN allergens a ON a.hotel_id=r.hotel_id AND a.code='EGG' WHERE mi.public_id='6CB0BF43FF51EA5030DD5DBED7'; INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='WHEAT' WHERE mi.public_id='9E98233AE705703F64B9C3FFB0'; INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='EGG' WHERE mi.public_id='9E98233AE705703F64B9C3FFB0'; INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='CHOCOLATE' WHERE mi.public_id='9E98233AE705703F64B9C3FFB0'; INSERT IGNORE INTO menu_item_allergens (menu_item_id,allergen_id) SELECT mi.id,a.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN allergens a ON a.hotel_id=r.hotel_id AND a.code='GLUTEN' WHERE mi.public_id='9E98233AE705703F64B9C3FFB0'; INSERT IGNORE INTO menu_item_allergens (menu_item_id,allergen_id) SELECT mi.id,a.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN allergens a ON a.hotel_id=r.hotel_id AND a.code='DAIRY' WHERE mi.public_id='9E98233AE705703F64B9C3FFB0'; INSERT IGNORE INTO menu_item_allergens (menu_item_id,allergen_id) SELECT mi.id,a.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN allergens a ON a.hotel_id=r.hotel_id AND a.code='EGG' WHERE mi.public_id='9E98233AE705703F64B9C3FFB0'; INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='SALMON' WHERE mi.public_id='45B7E59983A29AA06B9E119794'; INSERT IGNORE INTO menu_item_allergens (menu_item_id,allergen_id) SELECT mi.id,a.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN allergens a ON a.hotel_id=r.hotel_id AND a.code='FISH' WHERE mi.public_id='45B7E59983A29AA06B9E119794'; INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='COFFEE' WHERE mi.public_id='C029A15B7E66C71558A8841DC1'; INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='MILK' WHERE mi.public_id='C029A15B7E66C71558A8841DC1'; INSERT IGNORE INTO menu_item_allergens (menu_item_id,allergen_id) SELECT mi.id,a.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN allergens a ON a.hotel_id=r.hotel_id AND a.code='DAIRY' WHERE mi.public_id='C029A15B7E66C71558A8841DC1'; INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='CHICKEN' WHERE mi.public_id='7D01FAEF55670E2E3EF113E8A9'; INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='WHEAT' WHERE mi.public_id='7D01FAEF55670E2E3EF113E8A9'; INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='EGG' WHERE mi.public_id='7D01FAEF55670E2E3EF113E8A9'; INSERT IGNORE INTO menu_item_allergens (menu_item_id,allergen_id) SELECT mi.id,a.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN allergens a ON a.hotel_id=r.hotel_id AND a.code='GLUTEN' WHERE mi.public_id='7D01FAEF55670E2E3EF113E8A9'; INSERT IGNORE INTO menu_item_allergens (menu_item_id,allergen_id) SELECT mi.id,a.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN allergens a ON a.hotel_id=r.hotel_id AND a.code='EGG' WHERE mi.public_id='7D01FAEF55670E2E3EF113E8A9'; INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='WHEAT' WHERE mi.public_id='596747059E9B307CFFF1BC4A2A'; INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='EGG' WHERE mi.public_id='596747059E9B307CFFF1BC4A2A'; INSERT IGNORE INTO menu_item_allergens (menu_item_id,allergen_id) SELECT mi.id,a.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN allergens a ON a.hotel_id=r.hotel_id AND a.code='GLUTEN' WHERE mi.public_id='596747059E9B307CFFF1BC4A2A'; INSERT IGNORE INTO menu_item_allergens (menu_item_id,allergen_id) SELECT mi.id,a.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN allergens a ON a.hotel_id=r.hotel_id AND a.code='DAIRY' WHERE mi.public_id='596747059E9B307CFFF1BC4A2A'; INSERT IGNORE INTO menu_item_allergens (menu_item_id,allergen_id) SELECT mi.id,a.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN allergens a ON a.hotel_id=r.hotel_id AND a.code='EGG' WHERE mi.public_id='596747059E9B307CFFF1BC4A2A'; INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='CHICKEN' WHERE mi.public_id='FB1019D0E4B8855BB45B73FDFF'; INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='WHEAT' WHERE mi.public_id='FB1019D0E4B8855BB45B73FDFF'; INSERT IGNORE INTO menu_item_allergens (menu_item_id,allergen_id) SELECT mi.id,a.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN allergens a ON a.hotel_id=r.hotel_id AND a.code='GLUTEN' WHERE mi.public_id='FB1019D0E4B8855BB45B73FDFF'; INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='COFFEE' WHERE mi.public_id='A6998DC32DE56416B75FE6A159'; INSERT IGNORE INTO menu_item_ingredients (menu_item_id,ingredient_id) SELECT mi.id,i.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN ingredients i ON i.hotel_id=r.hotel_id AND i.code='MILK' WHERE mi.public_id='A6998DC32DE56416B75FE6A159'; INSERT IGNORE INTO menu_item_allergens (menu_item_id,allergen_id) SELECT mi.id,a.id FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN allergens a ON a.hotel_id=r.hotel_id AND a.code='DAIRY' WHERE mi.public_id='A6998DC32DE56416B75FE6A159'; INSERT IGNORE INTO menu_item_addons (menu_item_id,addon_id,required,min_qty,max_qty,display_order) SELECT mi.id,a.id,0,0,2,10 FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN addons a ON a.hotel_id=r.hotel_id AND a.code='EXTRA_CHEESE' WHERE mi.public_id='7D01FAEF55670E2E3EF113E8A9'; INSERT IGNORE INTO menu_item_addons (menu_item_id,addon_id,required,min_qty,max_qty,display_order) SELECT mi.id,a.id,0,0,2,20 FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN addons a ON a.hotel_id=r.hotel_id AND a.code='EXTRA_SAUCE' WHERE mi.public_id='7D01FAEF55670E2E3EF113E8A9'; INSERT IGNORE INTO menu_item_addons (menu_item_id,addon_id,required,min_qty,max_qty,display_order) SELECT mi.id,a.id,0,0,2,10 FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN addons a ON a.hotel_id=r.hotel_id AND a.code='EXTRA_CHEESE' WHERE mi.public_id='34E323769C8DCF16FFFBD742EF'; INSERT IGNORE INTO menu_item_addons (menu_item_id,addon_id,required,min_qty,max_qty,display_order) SELECT mi.id,a.id,0,0,2,20 FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN addons a ON a.hotel_id=r.hotel_id AND a.code='EXTRA_SAUCE' WHERE mi.public_id='34E323769C8DCF16FFFBD742EF'; INSERT IGNORE INTO menu_item_addons (menu_item_id,addon_id,required,min_qty,max_qty,display_order) SELECT mi.id,a.id,0,0,2,10 FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN addons a ON a.hotel_id=r.hotel_id AND a.code='EXTRA_CHEESE' WHERE mi.public_id='FB1019D0E4B8855BB45B73FDFF'; INSERT IGNORE INTO menu_item_addons (menu_item_id,addon_id,required,min_qty,max_qty,display_order) SELECT mi.id,a.id,0,0,2,20 FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id JOIN addons a ON a.hotel_id=r.hotel_id AND a.code='EXTRA_SAUCE' WHERE mi.public_id='FB1019D0E4B8855BB45B73FDFF'; INSERT INTO kitchen_routing (restaurant_id,menu_item_id,kitchen_id,station_id,priority,status) SELECT mi.restaurant_id,mi.id,r.kitchen_id,NULL,10,'ACTIVE' FROM menu_items mi JOIN restaurants r ON r.id=mi.restaurant_id WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.kitchen_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM kitchen_routing kr WHERE kr.menu_item_id=mi.id); -- Phase 3 database-driven ordering permissions. INSERT INTO permissions (code,name) VALUES ('orders.view','View orders'), ('orders.manage','Manage orders') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('SUPER_ADMIN','HOTEL_ADMIN','RESTAURANT_MANAGER','ROOM_SERVICE_MANAGER') AND p.code IN ('orders.view','orders.manage'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('WAITER','ROOM_SERVICE_STAFF','KITCHEN_MANAGER','READ_ONLY_MANAGER') AND p.code='orders.view'; -- Phase 5 room-service delivery configuration and permissions. INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'room_service_delivery_minutes','10','INTEGER' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'room_service_schedule_lead_minutes','30','INTEGER' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO permissions (code,name) VALUES ('room_service.view','View room-service delivery operations'), ('room_service.manage','Manage room-service delivery operations') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('SUPER_ADMIN','HOTEL_ADMIN','ROOM_SERVICE_MANAGER') AND p.code IN ('room_service.view','room_service.manage'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code='ROOM_SERVICE_STAFF' AND p.code IN ('room_service.view','room_service.manage'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code='READ_ONLY_MANAGER' AND p.code='room_service.view'; -- Phase 6 restaurant reservation settings and permissions. INSERT INTO reservation_settings (restaurant_id,enabled,slot_minutes,duration_minutes,turnover_minutes,hold_minutes,minimum_party_size,maximum_party_size,advance_days,max_combined_tables) SELECT r.id,1,30,90,15,5,1,20,30,3 FROM restaurants r WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.type IN ('RESTAURANT','CAFE') ON DUPLICATE KEY UPDATE restaurant_id=VALUES(restaurant_id); INSERT INTO permissions (code,name) VALUES ('reservations.view','View restaurant reservations'), ('reservations.manage','Manage restaurant reservations'), ('reservations.settings','Configure reservation settings') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('SUPER_ADMIN','HOTEL_ADMIN','RESTAURANT_MANAGER') AND p.code IN ('reservations.view','reservations.manage','reservations.settings'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code='WAITER' AND p.code IN ('reservations.view','reservations.manage'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('ROOM_SERVICE_MANAGER','READ_ONLY_MANAGER') AND p.code='reservations.view'; -- Phase 7 billing, payment and room-charge configuration. INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'room_charge_verification_method','STAFF_APPROVAL','STRING' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'online_payment_enabled','0','BOOLEAN' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO permissions (code,name) VALUES ('billing.view','View bills and bill allocations'), ('billing.manage','Manage bills and split allocations'), ('payments.view','View payment records'), ('payments.manage','Record and manage payments'), ('room_charge.approve','Approve or reject room-charge requests') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('SUPER_ADMIN','HOTEL_ADMIN','CASHIER','BILLING_STAFF') AND p.code IN ('billing.view','billing.manage','payments.view','payments.manage','room_charge.approve'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('RESTAURANT_MANAGER','ROOM_SERVICE_MANAGER','READ_ONLY_MANAGER') AND p.code IN ('billing.view','payments.view'); -- Phase 8 integration architecture and permissions. INSERT INTO permissions (code,name) VALUES ('integrations.view','View PMS, POS and ERP integration status and logs'), ('integrations.manage','Configure and operate PMS, POS and ERP integrations') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('SUPER_ADMIN','HOTEL_ADMIN','IT_ADMINISTRATOR') AND p.code IN ('integrations.view','integrations.manage'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code='READ_ONLY_MANAGER' AND p.code='integrations.view'; INSERT INTO integration_connections (public_id,hotel_id,integration_type,provider,adapter_key,display_name,status,config_json) SELECT LEFT(REPLACE(UUID(),'-',''),26),id,'PMS','MOCK','mock','Mock PMS','ACTIVE',JSON_OBJECT('mode','local_database') FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE provider=provider; INSERT INTO integration_connections (public_id,hotel_id,integration_type,provider,adapter_key,display_name,status,config_json) SELECT LEFT(REPLACE(UUID(),'-',''),26),id,'POS','NONE','null','POS Integration','DISABLED',JSON_OBJECT() FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE provider=provider; INSERT INTO integration_connections (public_id,hotel_id,integration_type,provider,adapter_key,display_name,status,config_json) SELECT LEFT(REPLACE(UUID(),'-',''),26),id,'ERP','NONE','null','ERP Integration','DISABLED',JSON_OBJECT() FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE provider=provider; INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'pms_adapter','mock','STRING' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'pos_adapter','null','STRING' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'erp_adapter','null','STRING' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO hotel_settings (hotel_id,setting_key,setting_value,value_type) SELECT id,'pms_auto_room_session_sync','1','BOOLEAN' FROM hotels WHERE code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE setting_value=setting_value; -- Phase 9 reporting, analytics and audit permissions. INSERT INTO permissions (code,name) VALUES ('reports.view','View operational, sales, tax and performance reports'), ('reports.export','Export reports to CSV'), ('audit.view','View audit history and security activity') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('SUPER_ADMIN','HOTEL_ADMIN') AND p.code IN ('reports.view','reports.export','audit.view'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code='READ_ONLY_MANAGER' AND p.code IN ('reports.view','audit.view'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('CASHIER','BILLING_STAFF') AND p.code='reports.view'; INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code='IT_ADMINISTRATOR' AND p.code='audit.view'; -- PHASE 11 DEMO ENGAGEMENT INSERT INTO permissions (code,name) VALUES ('promotions.view','View promotions, hotel events and engagement analytics'), ('promotions.manage','Manage promotions, hotel events and waiting-time cards') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('SUPER_ADMIN','HOTEL_ADMIN','RESTAURANT_MANAGER','ROOM_SERVICE_MANAGER') AND p.code IN ('promotions.view','promotions.manage'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code='READ_ONLY_MANAGER' AND p.code='promotions.view'; INSERT INTO promotions (public_id,hotel_id,restaurant_id,menu_item_id,title,subtitle,description,promotion_type,discount_type,discount_value,audience,start_at,end_at,weekday_mask,time_from,time_to,priority,status) SELECT 'PH11WEEKEND000000000000001',h.id,NULL,NULL,'Weekend Dining Highlights','Discover this weekend at Crowne Plaza Riyadh RDC','Demo promotional content for development/testing. Replace with hotel-approved campaigns before production.','WEEKEND_SPECIAL','NONE',0,'ALL',NOW(),DATE_ADD(NOW(),INTERVAL 180 DAY),48,NULL,NULL,50,'ACTIVE' FROM hotels h WHERE h.code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE title=VALUES(title),description=VALUES(description),status=VALUES(status); INSERT INTO hotel_events (public_id,hotel_id,restaurant_id,title,description,location,starts_at,ends_at,priority,status) SELECT 'PH11EVENT00000000000000001',h.id,r.id,'Upcoming Hotel Dining Experience','Demo upcoming event for testing the guest waiting-time experience. Replace with an approved hotel event.','Crowne Plaza Riyadh RDC',DATE_ADD(NOW(),INTERVAL 21 DAY),DATE_ADD(DATE_ADD(NOW(),INTERVAL 21 DAY),INTERVAL 3 HOUR),50,'ACTIVE' FROM hotels h LEFT JOIN restaurants r ON r.hotel_id=h.id AND r.code='WILDFIRE' WHERE h.code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE title=VALUES(title),starts_at=VALUES(starts_at),status=VALUES(status); INSERT INTO engagement_cards (public_id,hotel_id,promotion_id,event_id,restaurant_id,title,body,audience,min_wait_minutes,max_wait_minutes,cta_label,cta_type,display_order,status) SELECT 'PH11CARDWKND00000000000001',h.id,p.id,NULL,NULL,NULL,NULL,'ROOM_GUESTS',5,NULL,'Discover','LOCAL_URL',10,'ACTIVE' FROM hotels h JOIN promotions p ON p.hotel_id=h.id AND p.public_id='PH11WEEKEND000000000000001' WHERE h.code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE status=VALUES(status); UPDATE engagement_cards SET cta_url='/reservations' WHERE public_id='PH11CARDWKND00000000000001'; INSERT INTO engagement_cards (public_id,hotel_id,promotion_id,event_id,restaurant_id,title,body,audience,min_wait_minutes,max_wait_minutes,cta_label,cta_type,display_order,status) SELECT 'PH11CARDEVT000000000000001',h.id,NULL,e.id,e.restaurant_id,NULL,NULL,'ROOM_GUESTS',8,NULL,'Reserve a table','RESERVATION',20,'ACTIVE' FROM hotels h JOIN hotel_events e ON e.hotel_id=h.id AND e.public_id='PH11EVENT00000000000000001' WHERE h.code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE status=VALUES(status); -- Demo special dish engagement. Display-only promotion; it does not alter billing totals automatically. INSERT INTO promotions (public_id,hotel_id,restaurant_id,menu_item_id,title,subtitle,description,promotion_type,discount_type,discount_value,audience,start_at,end_at,weekday_mask,priority,status) SELECT 'PH11SPECIALDISH00000000001',h.id,r.id,mi.id,'Today''s Featured Dish','A suggestion while you wait','Demo special-dish promotion for development/testing. Replace with a hotel-approved promotion before production.','SPECIAL_DISH','NONE',0,'DINE_IN',NOW(),DATE_ADD(NOW(),INTERVAL 180 DAY),0,30,'ACTIVE' FROM hotels h JOIN restaurants r ON r.hotel_id=h.id AND r.code='MOSAIQUE' JOIN menu_items mi ON mi.restaurant_id=r.id AND mi.name='Chocolate Cake' WHERE h.code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE restaurant_id=VALUES(restaurant_id),menu_item_id=VALUES(menu_item_id),title=VALUES(title),status=VALUES(status); INSERT INTO engagement_cards (public_id,hotel_id,promotion_id,event_id,restaurant_id,title,body,audience,min_wait_minutes,max_wait_minutes,cta_label,cta_type,display_order,status) SELECT 'PH11CARDSPECIAL00000000001',h.id,p.id,NULL,p.restaurant_id,NULL,NULL,'DINE_IN',3,NULL,'View dish','MENU_ITEM',5,'ACTIVE' FROM hotels h JOIN promotions p ON p.hotel_id=h.id AND p.public_id='PH11SPECIALDISH00000000001' WHERE h.code='CP_RIYADH_RDC' ON DUPLICATE KEY UPDATE promotion_id=VALUES(promotion_id),restaurant_id=VALUES(restaurant_id),status=VALUES(status); -- Phase 12 commercial licensing permissions. No license is seeded; production requires a vendor-signed license. INSERT INTO permissions (code,name) VALUES ('license.view','View software license and deployment identity'), ('license.manage','Install and manage software license') ON DUPLICATE KEY UPDATE name=VALUES(name); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code IN ('SUPER_ADMIN','HOTEL_ADMIN','IT_ADMINISTRATOR') AND p.code IN ('license.view','license.manage'); INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.hotel_id=(SELECT id FROM hotels WHERE code='CP_RIYADH_RDC') AND r.code='READ_ONLY_MANAGER' AND p.code='license.view';