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;