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;